sql - 在 SQL Server 中使用 Join 获取此输出时出现问题
问题描述
我想显示这样的输出,
+------------+--------------+---------------------+---------------+-----------+--------------+-----------+
| segment_id | segment_name | segment_description | tot_questions | tot_marks | marks_obtain | neg_marks |
+------------+--------------+---------------------+---------------+-----------+--------------+-----------+
| 10006 | MCQ SECTION | | 5 | 20 | | -.5 |
| 10007 | Non-MCQ | | 5 | 20 | | 0 |
+------------+--------------+---------------------+---------------+-----------+--------------+-----------+
现在这是我将用于连接的表,
MockTestMaster(使用的列 tot_marks)
+---------+------------+-------------+-------------+
| test_id | test_name | total_Marks | template_id |
+---------+------------+-------------+-------------+
| 1 | MOCKTEST-X | 20 | 1 |
+---------+------------+-------------+-------------+
MockTestDetails(segment_id、segment_name、tot_questions)
+---------+------------+--------------+------------------------+
| test_id | section_id | section_name | total_section_question |
+---------+------------+--------------+------------------------+
| 1 | 10006 | MCQ SECTION | 5 |
| 1 | 10006 | MCQ SECTION | 5 |
| 1 | 10006 | MCQ SECTION | 5 |
| 1 | 10006 | MCQ SECTION | 5 |
| 1 | 10006 | MCQ SECTION | 5 |
| 1 | 10007 | Non-MCQ | 5 |
| 1 | 10007 | Non-MCQ | 5 |
| 1 | 10007 | Non-MCQ | 5 |
| 1 | 10007 | Non-MCQ | 5 |
| 1 | 10007 | Non-MCQ | 5 |
+---------+------------+--------------+------------------------+
prd_template_question_type(使用的列 neg_marks)
+-------------+-------------+----------------+----------------+
| template_id | is_mcq_type | is_nonmcq_type | marks_if_wrong |
+-------------+-------------+----------------+----------------+
| 1 | 1 | 0 | -0.5 |
| 1 | 0 | 1 | 0 |
+-------------+-------------+----------------+----------------+
prd_templatesection(我可以使用这个表,但在我的应用程序用户可以添加一个部分并且不会插入到这个表中,因为它会改变原始模板,所以我将该部分插入到 MockTestDetails 表中)
+-------------+------------+--------------+------------------------+-------------------+
| template_id | section_id | section_name | total_section_question | sectiontype_isMcq |
+-------------+------------+--------------+------------------------+-------------------+
| 1 | 10006 | MCQ SECTION | 5 | true |
| 1 | 10007 | Non-MCQ | 5 | false |
+-------------+------------+--------------+------------------------+-------------------+
现在获取我的输出的问题是表格的marks_if_wrong
列prd_template_question_type
。
现在这里是如何获得 neg_marks 的交易:如果ofsectiontype_isMcq
是prd_templatesection
,true
则抓取.marks_if_wrong
prd_template_question_type
我试过这个查询:
SELECT DISTINCT
md.section_id AS segment_id,
md.section_name AS segment_name,
'' AS segment_description,
md.total_section_question as tot_questions,
total_Marks AS tot_marks,
'' AS marks_obtain,
'' AS neg_marks
--prd_template_question_type.marks_if_wrong AS neg_marks
FROM
dbo.MocktestDetails md
INNER JOIN
dbo.MockTestMaster mm ON mm.test_id = md.test_id
INNER JOIN
prd_templatesection pts ON pts.template_id = mm.template_id
WHERE
mm.test_id = 1;
解决方案
您可以使用以下代码
WITH cte_sales AS (
SELECT DISTINCT md.section_id AS segment_id,
md.section_name AS segment_name,
'' AS segment_description,
md.total_section_question AS tot_questions,
total_Marks AS tot_marks,
'' AS marks_obtain,
pts.template_id AS template_id
--prd_template_question_type.marks_if_wrong AS neg_marks
FROM dbo.MocktestDetails md
INNER JOIN dbo.MockTestMaster mm
ON mm.test_id = md.test_id
INNER JOIN prd_templatesection pts
ON pts.template_id = mm.template_id
WHERE mm.test_id = 1
)
SELECT segment_id,
segment_name,
segment_description,
tot_questions,
tot_marks,
marks_obtain,
COALESCE(
(
SELECT TOP 1 marks_if_wrong
FROM prd_template_question_type AS ptqt
JOIN prd_templatesection AS pt
ON ptqt.template_id = pt.template_id
WHERE ptqt.template_id = cte.template_id
AND pt.section_id = cte.segment_id
),
'0'
)
FROM cte_sales AS cte
推荐阅读
- ios - pod 未更新,即使它显示可通过“pod outdated”获得更新
- javascript - 如何修复“本机主机已退出”
- uima - 使用 Ruta,在带注释的关键字的下一行中获取数据
- elixir - Phoenix 框架 - 节点模块安装
- android - R8 完整模式的真正作用是什么(积极优化)?
- javascript - 在 vuejs 中重新渲染之前的问题函数调用
- sas - SAS MACRO 错误:不支持在开放代码中嵌套 %IF 语句。%IF 被忽略
- windows - SMB Ubuntu 与 WIN 10 1903 共享 - 协议 3_11
- sql - 将数据从 Google Big Query 下载到 R 时如何防止格式错误(字符串变量)?
- java - 无法使用 spring JpaRepository 持久化数据