34,590
社区成员
发帖
与我相关
我的任务
分享
select a.* from t_im_flow a join (
select voucher_no ,item_no,count(0) PV from t_im_flow
group by voucher_no ,item_no
having count(0)>1
--这样也查不出你要的数据吗?
[/quote]
有语法错误
WITH t1(voucher_no ,item_no,x) AS
(
SELECT '1', '1', 'A' UNION ALL
SELECT '1', '1', 'B' UNION ALL
SELECT '1', '2', 'C'
),
t2 AS
(
SELECT voucher_no ,item_no FROM t1
GROUP BY voucher_no ,item_no
HAVING COUNT(1) = 2
)
SELECT * FROM t2
INNER JOIN t1
ON t1.voucher_no = t2.voucher_no
AND t1.item_no = t2.item_no
select a.* from t_im_flow a join (
select voucher_no ,item_no,count(0) PV from t_im_flow
group by voucher_no ,item_no
having count(0)>1
--这样也查不出你要的数据吗?
;with CTE AS (
select 1 as voucher_no ,2 as item_no ,'CCFFSFS' as Col union all
select 1 as voucher_no ,2 as item_no ,'Caafeafav' as Col union all
select 2 as voucher_no ,1 as item_no ,'vavaav' as Col union all
select 3 as voucher_no ,5 as item_no ,'43434242' as Col
)
select *
from CTE a
where exists(select 1 from CTE where voucher_no = a.voucher_no and item_no = a.item_no having count(*) > 1)
voucher_no item_no Col
----------- ----------- ---------
1 2 CCFFSFS
1 2 Caafeafav
select a.*
from t_im_flow a
inner join (select voucher_no,item_no
from t_im_flow
group by voucher_no ,item_no) b on a.voucher_no=b.voucher_no and a.item_no=b.item_no
order by a.voucher_no,a.item_no