work_pz_sj 表如下id che_cx
1 奥迪
2 奥迪
3 宝马
4 奔驰sm_peijcx 表如下
id chex_mc
1 奔驰
2 奥迪
3 宝马
我想要通过两个表对比求总和如下效果: 车型 数量
奥迪 2
宝马 1
奔驰 1 我现在的查询语句 是
select w.che_cx,s.chex_mc from work_pz_sj AS W,sm_peijcx AS S WHERE w.che_cx=s.chex_mc通过两个表的对比 得出 所有相同车型 但是现在的数据 没有个总和
请各位大虾帮忙解决!!
1 奥迪
2 奥迪
3 宝马
4 奔驰sm_peijcx 表如下
id chex_mc
1 奔驰
2 奥迪
3 宝马
我想要通过两个表对比求总和如下效果: 车型 数量
奥迪 2
宝马 1
奔驰 1 我现在的查询语句 是
select w.che_cx,s.chex_mc from work_pz_sj AS W,sm_peijcx AS S WHERE w.che_cx=s.chex_mc通过两个表的对比 得出 所有相同车型 但是现在的数据 没有个总和
请各位大虾帮忙解决!!
from (select che_cx fn,count(*)
from work_pz_sj group by che_cx) a
join (select chex_mc fn,count(*)
from sm_peijcx group by chex_mc) b on a.fn=b.fn
select a.fn,fc1,fc2
from (select che_cx fn,count(*) fc1
from work_pz_sj group by che_cx) a
join (select chex_mc fn,count(*) fc2
from sm_peijcx group by chex_mc) b on a.fn=b.fn
a.che_cx,count(a.che_cx) as 数量
from
work_pz_sj a join sm_peijcx b
on
a.che_cx=b.chex_mc
group by
a.che_cx
-- Author :fredrickhu(小F,向高手学习)
-- Date :2012-06-12 09:30:52
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[work_pz_sj]
if object_id('[work_pz_sj]') is not null drop table [work_pz_sj]
go
create table [work_pz_sj]([id] int,[che_cx] varchar(4))
insert [work_pz_sj]
select 1,'奥迪' union all
select 2,'奥迪' union all
select 3,'宝马' union all
select 4,'奔驰'
--> 测试数据:[sm_peijcx]
if object_id('[sm_peijcx]') is not null drop table [sm_peijcx]
go
create table [sm_peijcx]([id] int,[chex_mc] varchar(4))
insert [sm_peijcx]
select 1,'奔驰' union all
select 2,'奥迪' union all
select 3,'宝马'
--------------开始查询--------------------------
select
a.che_cx as 车型,count(a.che_cx) as 数量
from
work_pz_sj a join sm_peijcx b
on
a.che_cx=b.chex_mc
group by
a.che_cx----------------结果----------------------------
/* 车型 数量
---- -----------
奥迪 2
宝马 1
奔驰 1(3 行受影响)
*/
a.che_cx as 车型,count(a.che_cx) as 数量
from
work_pz_sj a join sm_peijcx b
on
a.che_cx=b.chex_mc
group by
a.che_cx
order by
2