大家好,请问在表结构如下的表A中,想查出按address排序且address相同次数越多的越靠前,这SQL要怎样写?如下:
表A:
查前:
orderid,productcode,address,quantity
001 NO1 上海 3
002 N01 重庆 3
003 NO1 上海 9
004 N01 北京 3
005 NO1 上海 3
006 N02 北京 5
007 NO2 南京 2
查后:
orderid,productcode,address,quantity
001 NO1 上海 3
003 NO1 上海 9
005 NO1 上海 3
004 N01 北京 3
006 N02 北京 5
002 N01 重庆 3
007 NO2 南京 2
表A:
查前:
orderid,productcode,address,quantity
001 NO1 上海 3
002 N01 重庆 3
003 NO1 上海 9
004 N01 北京 3
005 NO1 上海 3
006 N02 北京 5
007 NO2 南京 2
查后:
orderid,productcode,address,quantity
001 NO1 上海 3
003 NO1 上海 9
005 NO1 上海 3
004 N01 北京 3
006 N02 北京 5
002 N01 重庆 3
007 NO2 南京 2
A.*
FROM
A,
(SELECT address,COUNT(1) AS CNT FROM A GROUP BY address) AS B
WHERE
A.address=B.address
ORDER BY
B.CNT DESC,
ORDERID
(select address , count(1) cnt from a group by address) n
where m.address = n.address order by n.cnt desc , orderid
go
create table [A]([orderid] varchar(3),[productcode] varchar(3),[address] varchar(4),[quantity] int)
insert [A]
select '001','NO1','上海',3 union all
select '002','N01','重庆',3 union all
select '003','NO1','上海',9 union all
select '004','N01','北京',3 union all
select '005','NO1','上海',3 union all
select '006','N02','北京',5 union all
select '007','NO2','南京',2SELECT
A.*
FROM
A,
(SELECT address,COUNT(1) AS CNT FROM A GROUP BY address) AS B
WHERE
A.address=B.address
ORDER BY
B.CNT DESC,
ORDERID
--测试结果:
/*
orderid productcode address quantity
------- ----------- ------- -----------
001 NO1 上海 3
003 NO1 上海 9
005 NO1 上海 3
004 N01 北京 3
006 N02 北京 5
002 N01 重庆 3
007 NO2 南京 2(7 行受影响)*/
insert into a values('001', 'NO1' , '上海' , 3 )
insert into a values('002', 'NO1' , '重庆' , 3 )
insert into a values('003', 'NO1' , '上海' , 9 )
insert into a values('004', 'NO1' , '北京' , 3 )
insert into a values('005', 'NO1' , '上海' , 3 )
insert into a values('006', 'NO2' , '北京' , 5 )
insert into a values('007', 'NO2' , '南京' , 2 )
goselect m.* from a m,
(select address , count(1) cnt from a group by address) n
where m.address = n.address order by n.cnt desc , orderiddrop table a/*
orderid productcode address quantity
---------- ----------- ---------- -----------
001 NO1 上海 3
003 NO1 上海 9
005 NO1 上海 3
004 NO1 北京 3
006 NO2 北京 5
002 NO1 重庆 3
007 NO2 南京 2(所影响的行数为 7 行)
*/