0

## 【已解決】SQL 語法一問 (MSSQL)

``````例如以下幾筆資料
A      B
1     10    apple
2     10    pen
3     13    pig
4     13    pig
``````

10

### 3 個回答

2
rogeryao
iT邦大師 3 級 ‧ 2021-04-14 14:22:46

``````CREATE TABLE XX (
A int,B varchar(10));

INSERT INTO XX
Values(10,'apple'),
(10,'pen'),
(13,'pig'),
(13,'pig'),
(14,'MM');
``````
``````SELECT A
FROM (
SELECT DISTINCT A,B
FROM XX
) AS YY
GROUP BY A
HAVING COUNT(A)>1
``````

Demo

1
allenlwh
iT邦高手 1 級 ‧ 2021-04-14 14:01:21
``````CREATE TABLE [dbo].[_Tmp_0414](
[idx] [int] IDENTITY(1,1) NOT NULL,
[A] [nvarchar](10) NULL,
[B] [nvarchar](10) NULL
) ON [PRIMARY]

GO

select distinct A
,(select distinct B+',' from [_Tmp_0414] where A=t0.A for xml path(''))
as result
from [_Tmp_0414] t0
``````
``````A	result
10	apple,pen,
13	pen,
``````

1

iT邦高手 1 級 ‧ 2021-04-14 14:17:38

``````declare @Tmp table(
A nvarchar(50)
,B nvarchar(50)
)
insert into @Tmp
values('10','apple')
,('10','pen')
,('13','pig')
,('13','pig')

select *
from @Tmp a
group by A,B
having Count(0) = 1

select A
from (
select *
from @Tmp a
group by A,B
having Count(0) = 1
) k
group by A
``````

Count 是相同資料的次數~

Count(0)跟Count(*)