sql将历史表连接到活动表SSRS报告

发布于 2024-08-02 05:42:49 字数 537 浏览 6 评论 0原文

我正在尝试从需要 4 个表的数据库（sql server 2005）中提取结果：订阅者 S、会员 M、ClaimLines L、ClaimHistoryLines H

查询如下：

select S.SBSB_ID, M.MEME_NAME,
(CASE L.CLCL_ID WHEN '' THEN H.CLCL_ID ELSE L.CLCL_ID END) AS CLAIM_ID
FROM CMC_CDDL_CL_LINE L, CMC_MEME_MEMBER M LEFT OUTER JOIN CMC_CLDH_DEN_HIST H 
ON H.MEME_CK = M.MEME_CK, CMC_SBSB_SUBSC S
WHERE 
S.SBSB_ID = '120943270' AND
L.MEME_CK = M.MEME_CK AND
M.SBSB_CK = S.SBSB_CK

此查询成功从 ClaimLines L 表中提取结果行，但未显示 History 表中的结果。我不知道如何做到这一点，任何可以提供帮助的 sql 专家都会很棒。 -谢谢！

原文

I'm trying to pull results from a database (sql server 2005) which takes 4 tables:
Subscriber S, Member M, ClaimLines L, ClaimHistoryLines H

Query is as follows:

select S.SBSB_ID, M.MEME_NAME,
(CASE L.CLCL_ID WHEN '' THEN H.CLCL_ID ELSE L.CLCL_ID END) AS CLAIM_ID
FROM CMC_CDDL_CL_LINE L, CMC_MEME_MEMBER M LEFT OUTER JOIN CMC_CLDH_DEN_HIST H 
ON H.MEME_CK = M.MEME_CK, CMC_SBSB_SUBSC S
WHERE 
S.SBSB_ID = '120943270' AND
L.MEME_CK = M.MEME_CK AND
M.SBSB_CK = S.SBSB_CK

This query successfully pulls in the result rows from the ClaimLines L table but no results from the History table are shown. I'm not sure how to do this, any sql experts out there that can help would be great. -Thanks!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

浪菊怪哟 2024-08-09 05:42:49

CMC_CDDL_CL_LINE L、CMC_MEME_MEMBER M 左外连接 CMC_CLDH_DEN_HIST H

请勿将过时的隐含连接语法与左连接混合。他们在一起玩得不好。使用正确的 ANSII 标准连接语法。事实上完全停止使用过时的语法。

回复收藏 0 原文

少钕鈤記 2024-08-09 05:42:49

对我来说这是一个非常愚蠢的错误。我忽略了使用 UNION 来解决我的问题，并允许我从两个地方提取到一个结果集中，而不会创建大量重复行。很高兴有人指出使用 ANSII 标准编写 sql 的正确方法。

select S.SBSB_ID, M.MEME_NAME,
L.CLCL_ID AS CLAIM_ID
FROM
CMC_SBSB_SUBSC S INNER JOIN CMC_MEME_MEMBER M ON S.SBSB_CK = M.SBSB_CK
INNER JOIN CMC_CDDL_CL_LINE L ON L.MEME_CK = M.MEME_CK
WHERE 
S.SBSB_ID = '120943270'
UNION
select S.SBSB_ID, M.MEME_NAME,
H.CLCL_ID AS CLAIM_ID
FROM
CMC_SBSB_SUBSC S INNER JOIN CMC_MEME_MEMBER M ON S.SBSB_CK = M.SBSB_CK
INNER JOIN CMC_CLDH_DEN_HIST H ON H.MEME_CK = M.MEME_CK
WHERE 
S.SBSB_ID = '120943270'

This was a very silly mistake on my part. I neglected to think of using a UNION which solved my problem and allowed me to pull from both places into a single results set without creating massive duplicate rows. Glad someone pointed out the proper way to write sql using ANSII standards.

select S.SBSB_ID, M.MEME_NAME,
L.CLCL_ID AS CLAIM_ID
FROM
CMC_SBSB_SUBSC S INNER JOIN CMC_MEME_MEMBER M ON S.SBSB_CK = M.SBSB_CK
INNER JOIN CMC_CDDL_CL_LINE L ON L.MEME_CK = M.MEME_CK
WHERE 
S.SBSB_ID = '120943270'
UNION
select S.SBSB_ID, M.MEME_NAME,
H.CLCL_ID AS CLAIM_ID
FROM
CMC_SBSB_SUBSC S INNER JOIN CMC_MEME_MEMBER M ON S.SBSB_CK = M.SBSB_CK
INNER JOIN CMC_CLDH_DEN_HIST H ON H.MEME_CK = M.MEME_CK
WHERE 
S.SBSB_ID = '120943270'

回复收藏 0 原文

~没有更多了~