续昨天：问一问题SQL语句******

[blueteeth_yl]

昨天可能我没描述清楚，先 列出数据给大家

------------------------------------
id|name|DayType|Night|
1|name0|0|1|
2|name1|0|1|
3|name2|1|1|
4|name3|1|0|

select * from tb where DayType = 1 or Night = 1，现在要使daytype =1的排在 niight =1数据的前面而且同时 = 1，显示两次。

这样子：

3|name2|1|1|
4|name3|1|0|
1|name0|0|1|
2|name1|0|1|
3|name2|1|1|

按这个顺序排列


那位大仙给讲解下

[nianran520]

select * from tb 
where DayType = 1 or Night = 1
union all
select * from tb 
where DayType = 1 and Night = 1
order by DayType desc,Night desc

[ws_hgo]

create table #TT
(
  id int,
  [name] nvarchar(50),
  DayType int,
  Night int
)
insert into #TT select 1,'name0',0,1
insert into #TT select 2,'name1',0,1
insert into #TT select 3,'name2',1,1
insert into #TT select 4,'name3',1,0


select * from #TT where DayType=1
union all
select * from #TT where DayType=0
union all
select * from #TT where DayType=1 and Night=1

id          name                                               DayType     Night
----------- -------------------------------------------------- ----------- -----------
3           name2                                              1           1
4           name3                                              1           0
1           name0                                              0           1
2           name1                                              0           1
3           name2                                              1           1

(5 行受影响)

[nalnait]

DECLARE @T TABLE(id INT,name varchar(10),daytype INT,night INT)

INSERT INTO @T
SELECT 1,'name0',0,1 UNION ALL
SELECT 2,'name1',0,1 UNION ALL
SELECT 3,'name2',1,1 UNION ALL
SELECT 4,'name3',1,0

SELECT ID,name,daytype,night FROM
(
SELECT '1'XX,* FROM @T 
UNION ALL
SELECT '2',* FROM @T WHERE daytype=1 and night=1
)X
ORDER BY XX,daytype DESC,night DESC

--
/*
ID          name       daytype     night
----------- ---------- ----------- -----------
3           name2      1           1
4           name3      1           0
1           name0      0           1
2           name1      0           1
3           name2      1           1

(5 行受影响)

*/

[让你望见影子的墙]

---------------------------------
--  Author: HEROWANG(让你望见影子的墙)
--  Date  : 2010-01-09 09:21:47
--  blog  : blog.csdn.net/herowang
---------------------------------
 
IF OBJECT_ID('[tb]') IS NOT NULL 
    DROP TABLE [tb]
go
CREATE TABLE [tb] (id INT,name VARCHAR(5),DayType INT,Night INT)
INSERT INTO [tb]
SELECT 1,'name0',0,1 UNION ALL
SELECT 2,'name1',0,1 UNION ALL
SELECT 3,'name2',1,1 UNION ALL
SELECT 4,'name3',1,0

;            
with 
wang as(select *,row=case when daytype=1 and night=1 then 1 
                    when daytype=1 and night=0 then 2             
                    when daytype=0 and night=1 then 3  end
         from tb)
select * from wang
union all
select * from wang where row=1
order by row

id	name	DayType	Night	row
3	name2	1	1	1
3	name2	1	1	1
4	name3	1	0	2
1	name0	0	1	3
2	name1	0	1	3

[SQL77]

DECLARE @T TABLE(id INT,name varchar(10),daytype INT,night INT)

INSERT INTO @T
SELECT 1,'name0',0,1 UNION ALL
SELECT 2,'name1',0,1 UNION ALL
SELECT 3,'name2',1,1 UNION ALL
SELECT 4,'name3',1,0



SELECT     *  INTO #T FROM @T T WHERE daytype=1 OR night=1 ORDER BY daytype DESC,night DESC


SELECT * FROM #T 
UNION ALL

SELECT * FROM @T WHERE daytype=1 AND night=1


（所影响的行数为 4 行）

id          name       daytype     night       
----------- ---------- ----------- ----------- 
3           name2      1           1
4           name3      1           0
1           name0      0           1
2           name1      0           1
3           name2      1           1

（所影响的行数为 5 行）