简单问题：

[bh586]

表格如下：
id seq num
01 1 x
01 2 x
02 1 n
03 1 d
03 2 d
03 3 d
04 1 x
04 2 x
05 1 n
05 2 d
05 3 d
06 1 n
06 2 d
06 3 n


根据num的值 要求列出所有的id seq num组合
如果num='x',则 id seq num
01 '-' x
04 '-' x
如果num<>'x',根据num的值,分别列出。如果同一id的num相同
如：
id seq num
02 '-' n
03 '-' d
如果同一id的num不同
id seq num
05 1 n
05 2 d
05 3 d
06 1 n
06 2 d
06 3 n

[冷箫轻笛]

select distinct id,'-','x'
from tablename
where num = 'x'

union all

select id,'-',num f
from tablename
where num <> 'x'
group by id,num having count(1) =1

union all

select id,seq,num from tablename
where id in
(
select id
from tablename
where num <> 'x'
group by id,num having count(num) >= 2
)

[冷箫轻笛]

--修改一下

select distinct id,'-' as seq,'x'
from tablename
where num = 'x'

union all

select distinct id,'-',num
from tablename
where num <> 'x'
group by id,num having count(1) =1

union all

select id,seq,num from tablename
where id in
(
select id
from tablename
where num <> 'x'
group by id,num having count(num) >= 2
)

[bh586]

结果不符。
我想的结果是

01 '-' x
02 '-' n
03 '-' d
04 '-' x
05 1 n
05 2 d
05 3 d
06 1 n
06 2 d
06 3 n

[bh586]

表格如下：
id seq num
01 1 x
01 2 x
02 1 n
03 1 d
03 2 d
03 3 d
04 1 x
04 2 x
05 1 n
05 2 d
05 3 d
06 1 n
06 2 d
06 3 n


根据num的值 要求列出所有的id seq num组合,如果同一id的num相同
如：
id seq num
01 '-' x
02 '-' n
03 '-' d
04 '-' x

如果同一id的num不同
id seq num
05 1 n
05 2 d
05 3 d
06 1 n
06 2 d
06 3 n
最后的结果是：
01 '-' x
02 '-' n
03 '-' d
04 '-' x
05 1 n
05 2 d
05 3 d
06 1 n
06 2 d
06 3 n

[paoluo]

--建立測試環境
Create Table TEST
(id Varchar(2),
seq Int,
num Varchar(10))
--插入數據
Insert TEST Select'01', 1, 'x'
Union All Select '01', 2, 'x'
Union All Select '02', 1, 'n'
Union All Select '03', 1, 'd'
Union All Select '03', 2, 'd'
Union All Select '03', 3, 'd'
Union All Select '04', 1, 'x'
Union All Select '04', 2, 'x'
Union All Select '05', 1, 'n'
Union All Select '05', 2, 'd'
Union All Select '05', 3, 'd'
Union All Select '06', 1, 'n'
Union All Select '06', 2, 'd'
Union All Select '06', 3, 'n'
--測試
Select Distinct id,'-' As seq,num from TEST A
Where Not Exists (Select 1 from TEST Where id=A.id And num<>A.num)
Union All
Select id,Rtrim(seq),num from TEST A
Where Exists (Select 1 from TEST Where id=A.id And num<>A.num)
Order By id
--刪除測試環境
Drop Table TEST
--結果
/*
id seq num
01 - x
02 - n
03 - d
04 - x
05 1 n
05 2 d
05 3 d
06 1 n
06 2 d
06 3 n
*/