表A:
CREATE TABLE [test] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[n_brand] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[state] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[cname] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[ctime] [datetime] NULL CONSTRAINT [DF_test_ctime] DEFAULT (getdate()),
CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
inert into test (n_brand,state,cname) values ('诺基亚','维修中','张三')
inert into test (n_brand,state,cname) values ('三星','维修中','张三1')
inert into test (n_brand,state,cname) values ('诺基亚','维修中','张三2')
inert into test (n_brand,state,cname) values ('诺基亚','维修中','张三3')
inert into test (n_brand,state,cname) values ('索尼爱立信','维修中','张三4')
inert into test (n_brand,state,cname) values ('摩托罗拉','维修中','张三5')
inert into test (n_brand,state,cname) values ('三星','维修中','张三6')
inert into test (n_brand,state,cname) values ('诺基亚','维修中','张三7')
inert into test (n_brand,state,cname) values ('摩托罗拉','维修中','张三8')
inert into test (n_brand,state,cname) values ('三星','维修中','张三9')
现在要列表显示报修的品牌,要求按报修数量从大到小排列。就是诺基亚有4条报修记录它排第一,其次是三星的3条。
说清楚了吧?
谢谢大家!
CREATE TABLE [test] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[n_brand] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[state] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[cname] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[ctime] [datetime] NULL CONSTRAINT [DF_test_ctime] DEFAULT (getdate()),
CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
inert into test (n_brand,state,cname) values ('诺基亚','维修中','张三')
inert into test (n_brand,state,cname) values ('三星','维修中','张三1')
inert into test (n_brand,state,cname) values ('诺基亚','维修中','张三2')
inert into test (n_brand,state,cname) values ('诺基亚','维修中','张三3')
inert into test (n_brand,state,cname) values ('索尼爱立信','维修中','张三4')
inert into test (n_brand,state,cname) values ('摩托罗拉','维修中','张三5')
inert into test (n_brand,state,cname) values ('三星','维修中','张三6')
inert into test (n_brand,state,cname) values ('诺基亚','维修中','张三7')
inert into test (n_brand,state,cname) values ('摩托罗拉','维修中','张三8')
inert into test (n_brand,state,cname) values ('三星','维修中','张三9')
现在要列表显示报修的品牌,要求按报修数量从大到小排列。就是诺基亚有4条报修记录它排第一,其次是三星的3条。
说清楚了吧?
谢谢大家!
[id] [int] IDENTITY (1, 1) NOT NULL ,
[n_brand] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[state] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[cname] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[ctime] [datetime] NULL CONSTRAINT [DF_test_ctime] DEFAULT (getdate()),
CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into test (n_brand,state,cname) values ('诺基亚','维修中','张三')
insert into test (n_brand,state,cname) values ('三星','维修中','张三1')
insert into test (n_brand,state,cname) values ('诺基亚','维修中','张三2')
insert into test (n_brand,state,cname) values ('诺基亚','维修中','张三3')
insert into test (n_brand,state,cname) values ('索尼爱立信','维修中','张三4')
insert into test (n_brand,state,cname) values ('摩托罗拉','维修中','张三5')
insert into test (n_brand,state,cname) values ('三星','维修中','张三6')
insert into test (n_brand,state,cname) values ('诺基亚','维修中','张三7')
insert into test (n_brand,state,cname) values ('摩托罗拉','维修中','张三8')
insert into test (n_brand,state,cname) values ('三星','维修中','张三9') select n_brand from test group by n_brand
order by count(1) desc/*
n_brand
--------------------------------------------------
诺基亚
三星
摩托罗拉
索尼爱立信(4 row(s) affected)
*/
drop table test
[id] [int] IDENTITY (1, 1) NOT NULL ,
[n_brand] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[state] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[cname] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[ctime] [datetime] NULL CONSTRAINT [DF_test_ctime] DEFAULT (getdate()),
CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into test (n_brand,state,cname) values ('诺基亚','维修中','张三')
insert into test (n_brand,state,cname) values ('三星','维修中','张三1')
insert into test (n_brand,state,cname) values ('诺基亚','维修中','张三2')
insert into test (n_brand,state,cname) values ('诺基亚','维修中','张三3')
insert into test (n_brand,state,cname) values ('索尼爱立信','维修中','张三4')
insert into test (n_brand,state,cname) values ('摩托罗拉','维修中','张三5')
insert into test (n_brand,state,cname) values ('三星','维修中','张三6')
insert into test (n_brand,state,cname) values ('诺基亚','维修中','张三7')
insert into test (n_brand,state,cname) values ('摩托罗拉','维修中','张三8')
insert into test (n_brand,state,cname) values ('三星','维修中','张三9') select n_brand, n=count(1) from test group by n_brand
order by n desc/*
n_brand n
-------------------------------------------------- -----------
诺基亚 4
三星 3
摩托罗拉 2
索尼爱立信 1(4 row(s) affected)
*/
drop table test
select n_brand,count(n_brand) as cou from tb group by n_brand order by count(n_brand) desc
select a.* from test a join (
select n_brand,count(1) as oid from test group by n_brand)b
on a.n_brand=b.n_brand
order by b.oid descid n_brand state cname ctime
1 诺基亚 维修中 张三 2008-07-11 09:24:15.000
3 诺基亚 维修中 张三2 2008-07-11 09:24:15.000
4 诺基亚 维修中 张三3 2008-07-11 09:24:15.000
8 诺基亚 维修中 张三7 2008-07-11 09:24:15.000
2 三星 维修中 张三1 2008-07-11 09:24:15.000
10 三星 维修中 张三9 2008-07-11 09:24:15.000
7 三星 维修中 张三6 2008-07-11 09:24:15.000
6 摩托罗拉 维修中 张三5 2008-07-11 09:24:15.000
9 摩托罗拉 维修中 张三8 2008-07-11 09:24:15.000
5 索尼爱立信 维修中 张三4 2008-07-11 09:24:15.000
select n_brand ,count(*) CNT from test
group by n_brand
order by CNT desc
[id] [int] IDENTITY (1, 1) NOT NULL ,
[n_brand] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[state] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[cname] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[ctime] [datetime] NULL CONSTRAINT [DF_test_ctime] DEFAULT (getdate()),
CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into test (n_brand,state,cname) values ('诺基亚','维修中','张三')
insert into test (n_brand,state,cname) values ('三星','维修中','张三1')
insert into test (n_brand,state,cname) values ('诺基亚','维修中','张三2')
insert into test (n_brand,state,cname) values ('诺基亚','维修中','张三3')
insert into test (n_brand,state,cname) values ('索尼爱立信','维修中','张三4')
insert into test (n_brand,state,cname) values ('摩托罗拉','维修中','张三5')
insert into test (n_brand,state,cname) values ('三星','维修中','张三6')
insert into test (n_brand,state,cname) values ('诺基亚','维修中','张三7')
insert into test (n_brand,state,cname) values ('摩托罗拉','维修中','张三8')
insert into test (n_brand,state,cname) values ('三星','维修中','张三9')
select n_brand from test
group by n_brand
order by count(1) desc诺基亚
三星
摩托罗拉
索尼爱立信光要品牌?
CREATE TABLE [test] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[n_brand] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[state] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[cname] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[ctime] [datetime] NULL CONSTRAINT [DF_test_ctime] DEFAULT (getdate()),
CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GO drop table testinsert into test (n_brand,state,cname) values ('诺基亚','维修中','张三')
insert into test (n_brand,state,cname) values ('三星','维修中','张三1')
insert into test (n_brand,state,cname) values ('诺基亚','维修中','张三2')
insert into test (n_brand,state,cname) values ('诺基亚','维修中','张三3')
insert into test (n_brand,state,cname) values ('索尼爱立信','维修中','张三4')
insert into test (n_brand,state,cname) values ('摩托罗拉','维修中','张三5')
insert into test (n_brand,state,cname) values ('三星','维修中','张三6')
insert into test (n_brand,state,cname) values ('诺基亚','维修中','张三7')
insert into test (n_brand,state,cname) values ('摩托罗拉','维修中','张三8')
insert into test (n_brand,state,cname) values ('三星','维修中','张三9')
select * from test a
order by (select count(1) from test b where a.n_brand=b.n_brand) desc
------------
诺基亚
三星
摩托罗拉
索尼爱立信
*/
select a.* from test a order by (select count(*) from test where n_brand=a.n_brand) desc
/*
---------------------------
1 诺基亚 维修中 张三 2008-07-11 09:34:04.937
3 诺基亚 维修中 张三2 2008-07-11 09:34:04.937
4 诺基亚 维修中 张三3 2008-07-11 09:34:04.937
8 诺基亚 维修中 张三7 2008-07-11 09:34:04.937
7 三星 维修中 张三6 2008-07-11 09:34:04.937
2 三星 维修中 张三1 2008-07-11 09:34:04.937
10 三星 维修中 张三9 2008-07-11 09:34:04.937
6 摩托罗拉 维修中 张三5 2008-07-11 09:34:04.937
9 摩托罗拉 维修中 张三8 2008-07-11 09:34:04.937
5 索尼爱立信 维修中 张三4 2008-07-11 09:34:04.937
*/
order by n desc
from test
group by n_brand
order by count(1) desc
select n_brand,count(*) num
from test where state='维修中'
group by n_brand
order by num desc
n_brand num
-------------------------------------------------- -----------
诺基亚 4
三星 3
摩托罗拉 2
索尼爱立信 1(所影响的行数为 4 行)
group by order by
*/
CREATE TABLE [test] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[n_brand] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[state] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[cname] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[ctime] [datetime] NULL CONSTRAINT [DF_test_ctime] DEFAULT (getdate()),
CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into test (n_brand,state,cname) values ('诺基亚','维修中','张三')
insert into test (n_brand,state,cname) values ('三星','维修中','张三1')
insert into test (n_brand,state,cname) values ('诺基亚','维修中','张三2')
insert into test (n_brand,state,cname) values ('诺基亚','维修中','张三3')
insert into test (n_brand,state,cname) values ('索尼爱立信','维修中','张三4')
insert into test (n_brand,state,cname) values ('摩托罗拉','维修中','张三5')
insert into test (n_brand,state,cname) values ('三星','维修中','张三6')
insert into test (n_brand,state,cname) values ('诺基亚','维修中','张三7')
insert into test (n_brand,state,cname) values ('摩托罗拉','维修中','张三8')
insert into test (n_brand,state,cname) values ('三星','维修中','张三9') select n_brand from test group by n_brand order by count(1) desc