34,590
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([companyno] nvarchar(21),[companyname] nvarchar(22),[bankno] nvarchar(23),[bankname] nvarchar(24),[cgcompanyno] nvarchar(21),[cgcompanyname] nvarchar(22),[companycgje] int)
Insert #T
select N'A',N'小米',N'A01',N'建行银行',N'C',N'红米',500 union all
select N'B',N'大米',N'A01',N'建行银行',N'D',N'紫米',400 union all
select N'B',N'大米',N'A01',N'建行银行',N'E',N'黄米',400 union all
select N'A',N'小米',N'B01',N'农业银行',N'C',N'红米',300 union all
select N'B',N'大米',N'B01',N'农业银行',N'D',N'紫米',400 union all
select N'F',N'同仁',N'A01',N'建行银行',N'C',N'红米',500 union all
select N'F',N'同仁',N'B01',N'农业银行',N'C',N'红米',500
Go
--测试数据结束
SELECT * FROM (
SELECT
*,
CASE
WHEN
(
SELECT
COUNT(*)
FROM
#T b
WHERE a.companyno=b.companyno AND a.companyname=b.companyname
GROUP BY
companyno,
companyname
) <> 2
THEN '记录数不一致'
WHEN EXISTS
(
SELECT
*
FROM
#T b
WHERE
a.companyno = b.companyno
AND a.companyname = b.companyname
AND a.cgcompanyno = b.cgcompanyno
AND a.cgcompanyname = b.cgcompanyname
AND a.companycgje <> b.companycgje
)
THEN '金额错误'
ELSE
''
END AS jielun
FROM
#T a)t
WHERE t.jielun <>''