prod:产品表
pid title
1 测试产品1
2 测试产品2
3 测试产品3
4 测试产品4
5 测试产品5
paixu:产品排序表
pid seq
4 1
3 2
要求:
读取产品表中的所有产品,但是排序方式,先按照排序表中的seq字段升序排序,如果产品没有在排序表中设置排序,再按照产品表中的pid字段升序排序,以上面的例子得出结果为:测试产品4
测试产品3
测试产品1
测试产品2
测试产品5
pid title
1 测试产品1
2 测试产品2
3 测试产品3
4 测试产品4
5 测试产品5
paixu:产品排序表
pid seq
4 1
3 2
要求:
读取产品表中的所有产品,但是排序方式,先按照排序表中的seq字段升序排序,如果产品没有在排序表中设置排序,再按照产品表中的pid字段升序排序,以上面的例子得出结果为:测试产品4
测试产品3
测试产品1
测试产品2
测试产品5
select title from 产品表 a
join 排序表 b on a.pid=b.pid
join 排序表 c on a.pid=c.seq
order by b.pid,c.seq
prod A
LEFT JOIN paixu B ON A.PID=B.PID ORDER BY ISNULL(B.PID ,A.PID)DESC
标准的
select title from 产品表 a
join 排序表 b on a.pid=b.pid
join 排序表 c on a.pid=c.seq
order by b.pid desc,c.seq asc --漏了
-->Author:wufeng4552【水族杰纶】
-->Date :2009-08-21 11:07:47
if not object_id('prod') is null
drop table prod
Go
Create table prod([pid] int,[title] nvarchar(5))
Insert prod
select 1,N'测试产品1' union all
select 2,N'测试产品2' union all
select 3,N'测试产品3' union all
select 4,N'测试产品4' union all
select 5,N'测试产品5'
Go
if not object_id('paixu') is null
drop table paixu
Go
Create table paixu([pid] int,[seq] int)
Insert paixu
select 4,1 union all
select 3,2
Go
select a.* from prod a left join paixu b on a.[pid]=b.[pid]
order by case when seq is not null then -b.[pid] else a.[pid] end
/*
pid title
----------- -----
4 测试产品4
3 测试产品3
1 测试产品1
2 测试产品2
5 测试产品5
*/
-- Author: HEROWANG(让你望见影子的墙)
-- Date : 2009-08-21 11:07:47
---------------------------------
IF OBJECT_ID('[prod]') IS NOT NULL
DROP TABLE [prod]
go
CREATE TABLE [prod] (pid INT,title VARCHAR(9))
INSERT INTO [prod]
SELECT 1,'测试产品1' UNION ALL
SELECT 2,'测试产品2' UNION ALL
SELECT 3,'测试产品3' UNION ALL
SELECT 4,'测试产品4' UNION ALL
SELECT 5,'测试产品5'---------------------------------
-- Author: HEROWANG(让你望见影子的墙)
-- Date : 2009-08-21 11:08:06
---------------------------------
IF OBJECT_ID('[paixu]') IS NOT NULL
DROP TABLE [paixu]
go
CREATE TABLE [paixu] (pid INT,seq INT)
INSERT INTO [paixu]
SELECT 4,1 UNION ALL
SELECT 3,2
select s.* from prod s left join paixu t on s.pid=t.pid
order by isnull(seq,(select count(*) from prod )) ,s.pidpid title
4 测试产品4
3 测试产品3
1 测试产品1
2 测试产品2
5 测试产品5
--> 测试数据:[prod]
if object_id('[prod]') is not null drop table [prod]
create table [prod]([pid] int,[title] varchar(9))
insert [prod]
select 1,'测试产品1' union all
select 2,'测试产品2' union all
select 3,'测试产品3' union all
select 4,'测试产品4' union all
select 5,'测试产品5'
--> 测试数据:[paixu]
if object_id('[paixu]') is not null drop table [paixu]
create table [paixu]([pid] int,[seq] int)
insert [paixu]
select 4,1 union all
select 3,2
select * from [prod] where pid in(select pid from paixu) union all
select * from [prod] where pid not in(select pid from paixu)
/*
pid title
----------- ---------
3 测试产品3
4 测试产品4
1 测试产品1
2 测试产品2
5 测试产品5(5 行受影响)*/
SELECT A.* FROM
prod A
LEFT JOIN paixu B ON A.PID=B.PID ORDER BY ISNULL(B.seq ,999999),A.PID
select 1,'测试产品1' union all
select 2,'测试产品2' union all
select 3,'测试产品3' union all
select 4,'测试产品4' union all
select 5,'测试产品5'
declare @paixu table (pid int, seq int)insert into @paixu
select 4,1 union allselect 3,2 select title from @paixu as a join @prod as b on a.pid=b.pid order by a.seq ascselect title from @prod where title not in (select b.title from @paixu as a join @prod as b on a.pid=b.pid ) order by pid asc
最后两个查询怎么不能 union all 呢?
select 1,'测试产品1' union all
select 2,'测试产品2' union all
select 3,'测试产品3' union all
select 4,'测试产品4' union all
select 5,'测试产品5'
declare @paixu table (pid int, seq int) insert into @paixu
select 4,1 union all select 3,2 select title from @paixu as a join @prod as b on a.pid=b.pid order by a.seq asc select title from @prod where title not in (select b.title from @paixu as a join @prod as b on a.pid=b.pid ) order by pid asc
最后两个查询怎么不能 union all 呢?