问题描述
我有2个表...除了其中一个表中的列名外，它们基本上是相同的，因为它们处理2个不同的名称，但是我想要的数据在具有相同名称的列中.我想做的几乎是...它们也需要与众不同，因此我不计算重复项...我可以将它们作为单独的表保存...但是我不能将它们放在一起..我需要因为它们是如何发送到C3代码页以及如何读取的，所以它们在1列中....先前已经设置了该结构..在这样的一个存储过程中还执行了大约5条其他语句(同样，我不是那个人).所以，如果可能的话..请让我知道..如果您想查看真实的代码，我还将粘贴我拥有的确切代码..我只是试图简化它以使其更易于理解.我将发布一些图片，希望可以更好地解释它.
I have 2 tables...they are basically the same except for the column name in one of them because they deal with 2 different names, though.. the data I want is in columns that have the same name. Pretty much what I want to do... is .they also need to be distinct so I don't count duplicates... I can get them as separate tables... but I can't get them together.. I need them in 1 column because of how it is sent to the C3 code page and how it reads it... the structure has already been previously set.. and there are about 5 other statements that are being executed in this one stored procedure like this (also I wasn't the one who set this up). So if this is possible.. let me know please.. I will also paste the exact code I have if you feel like seeing the real life code.. I just tried to simplify it to make it easier to understand. I'm going to post some images to hopefully explain it a little better.
图像1是当前设置的图像:
Image 1 is what is currently set up:
顶部是存储在表中的内容.底部是更多结果它基本上运行此代码来获得最底端的
top part is what is stored in tables.. bottom is more of the resultit basically runs this code to get the bottom
DECLARE @id INT;
DECLARE @invest nvarchar(50);
SET @id = '7633';
SET @invest = '';
SELECT 'a' + CONVERT(nvarchar, orderfindings.risk_rating) AS cat, COUNT(DISTINCT orderfindings.prnt_id) AS stat
FROM orderheader, orderaudits, orderfindings
WHERE orderheader.id = orderaudits.orderheader_id AND orderaudits.ID = orderfindings.prnt_id
AND orderheader.id = @id AND orderfindings.risk_rating > 0 AND orderaudits.Investor_Name LIKE '%' + @invest + '%'
GROUP BY orderfindings.risk_rating
如果我要代理商而不是调查结果..只需替换一下..代理商和调查结果是2个表格..它们在列上几乎是相同的...但是我想将结果结合在一起..我已经尝试了几次方式..但我似乎无法理解图片2-底部的表格更是我想要的..它将它们都合并为1:
If I want agencies instead of findings.. just replace it.. agencies and findings are the 2 tables.. they are the pretty much identical column wise... but I want the result together.. I've tried several ways.. but I can't seem to get itimage 2- the table at the bottom is more what I'm looking for.. it combines them both into 1:
如果订单中包含调查结果或代理机构或两者兼有..则该订单的风险等级标记为1 ...如果没有，则为该风险等级标记0 ..然后将它们求和一切都看我得到了..任何帮助表示感谢..我会澄清一下，如果您不理解我该怎么做；
If an order has a finding or agency or both in it.. then it gets marked as a 1 for that risk rating... if it doesn't.. then 0 for that risk rating.. and then sum them all up to see what I got.. any help is appreciated .. I'll clarify what I can if you don't understand;
我一直在使用它.. .did这个 http://pastebin.com/117Z2TVh ..它显示2列..但是仍然不是正确的结果...我得到的是a1 = 1 ... a2 = 1 ...所以它没有遍历所有订单...或者它需要一种计数的方法它...我在案件陈述的开头加了一笔....由于计数有误...所以我把计数都拿走了...真的没有用..有什么建议吗?
I've been working with it.. .did this http://pastebin.com/117Z2TVh .. got it to display 2 columns.. but still not the right result... I'm getting a1 = 1...a2 = 1... so its not running through all the orders... or it needs a way to count it... I put a sum at beginning of case statement.. erro because of counts... so I took counts out... didn't really work.. any advice?
推荐答案
;查找AS(选择oa.id，oa.Risk_Rating，COUNT(orf.Prnt_ID)的findcount 从orderaudits oa LEFT JOIN订单查找orf 在oa.ID = orf.Prnt_ID上 --WHERE oa.risk_rating> 0和oa.Investor_Name如'％'+ @invest +'％' GROUP BY oa.id，oa.Risk_Rating)， 代理商AS(选择oa.id，oa.Risk_Rating，COUNT(ora.Prnt_ID)代理商计数 从orderaudits oa 左联接订单ora 在oa.ID = ora.Prnt_ID上 --WHERE oa.risk_rating> 0和oa.Investor_Name如'％'+ @invest +'％' GROUP BY oa.id，oa.Risk_Rating)/*选择``仅查找''，oa.Risk_Rating，COUNT(oa.id)作为a，SUM(当f.findingcount> 0然后1 ELSE 0 END的情况下)作为b 从orderaudits oa 左联接查找f 开启oa.id = f.id 左加入订单头哦 开启oh.id = oa.orderheader_id哪里oh.id = @id和oa.risk_rating> 0以及oa.Investor_Name像'％'+ @invest +'％'GROUP BY oa.Risk_Rating
;WITH finding AS (select oa.id, oa.Risk_Rating, COUNT (orf.Prnt_ID) findingcount FROM orderaudits oa LEFT JOIN orderfindings orf on oa.ID = orf.Prnt_ID --WHERE oa.risk_rating > 0 and oa.Investor_Name like '%' + @invest + '%' GROUP BY oa.id, oa.Risk_Rating), Agency AS (select oa.id, oa.Risk_Rating, COUNT (ora.Prnt_ID) agencycount FROM orderaudits oa LEFT JOIN orderagencies ora on oa.ID = ora.Prnt_ID --WHERE oa.risk_rating > 0 and oa.Investor_Name like '%' + @invest + '%' GROUP BY oa.id, oa.Risk_Rating)/*SELECT 'Finding Only', oa.Risk_Rating, COUNT(oa.id) as a, SUM(CASE WHEN f.findingcount > 0 then 1 ELSE 0 END ) as b FROM orderaudits oa LEFT JOIN finding f ON oa.id = f.id LEFT JOIN orderheader oh ON oh.id = oa.orderheader_idWHERE oh.id = @id and oa.risk_rating > 0 and oa.Investor_Name like '%' + @invest + '%'GROUP BY oa.Risk_Rating
全部联盟
选择'Agency Only'，oa.Risk_Rating，COUNT(oa.id)as a，SUM(当a.agencycount> 0然后1 ELSE 0 END的情况下)作为b 从orderaudits oa LEFT JOIN代理商a 开启oa.id = a.id 左加入订单头哦 开启oh.id = oa.orderheader_id哪里oh.id = @id和oa.risk_rating> 0以及oa.Investor_Name像'％'+ @invest +'％'GROUP BY oa.Risk_Rating
SELECT 'Agency Only', oa.Risk_Rating, COUNT(oa.id) as a, SUM(CASE WHEN a.agencycount > 0 then 1 ELSE 0 END ) as b FROM orderaudits oa LEFT JOIN Agency a ON oa.id = a.id LEFT JOIN orderheader oh ON oh.id = oa.orderheader_idWHERE oh.id = @id and oa.risk_rating > 0 and oa.Investor_Name like '%' + @invest + '%'GROUP BY oa.Risk_Rating
全部联盟*/ - 一起选择'aa'+ convert(nvarchar，oa.risk_rating)as cat，SUM(当f.findingcount> 0或a.agencycount> 0然后1 ELSE 0 END时的情况)作为b 从orderaudits oa 左联接查找f 开启oa.id = f.id LEFT JOIN代理商a 开启oa.id = a.id 左加入订单头哦 开启oh.id = oa.orderheader_id哪里oh.id = @id和oa.risk_rating> 0以及oa.Investor_Name像'％'+ @invest +'％'GROUP BY oa.Risk_Rating
UNION ALL*/--TogetherSELECT 'aa' + convert(nvarchar, oa.risk_rating) as cat, SUM(CASE WHEN f.findingcount > 0 OR a.agencycount > 0 then 1 ELSE 0 END ) as b FROM orderaudits oa LEFT JOIN finding f ON oa.id = f.id LEFT JOIN Agency a ON oa.id = a.id LEFT JOIN orderheader oh ON oh.id = oa.orderheader_idWHERE oh.id = @id and oa.risk_rating > 0 and oa.Investor_Name like '%' + @invest + '%'GROUP BY oa.Risk_Rating
这篇关于检查2个SQL列并在1列pt2中显示结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！