求一则常用sql

[zhangjidong]

原始表：
--------------------------
企业销量表：
etprs_id,monthcode,amount
企业1,200801,1
企业1,200802,1
企业2,200802,6

月份表 ：
monthcode
200801
200802
200803
-------------------------
希望得到结果：
企业1,200801,1
企业1,200802,1
企业1,200803,0
企业2,200801,0
企业2,200802,6
企业2,200803,0
也就是没有数据的月份，企业的销量置为0

[liangCK]

join一下．

[liangCK]

--> 测试数据: #T1
if object_id('tempdb.dbo.#T1') is not null drop table #T1
create table #T1 (etprs_id nvarchar(5),monthcode int,amount int)
insert into #T1
select '企业1',200801,1 union all
select '企业1',200802,1 union all
select '企业2',200802,6
--> 测试数据: #T2
if object_id('tempdb.dbo.#T2') is not null drop table #T2
create table #T2 (monthcode int)
insert into #T2
select 200801 union all
select 200802 union all
select 200803

;
with cte as
(
  select *
  from (select distinct etprs_id from #T1) a
    cross join #T2 b
)
select a.*,isnull(b.amount,0) amount
from cte a
 left join #T1 b
   on a.monthcode=b.monthcode and a.etprs_id=b.etprs_id

／＊
etprs_id monthcode   amount
-------- ----------- -----------
企业1      200801      1
企业1      200802      1
企业1      200803      0
企业2      200801      0
企业2      200802      6
企业2      200803      0

(6 行受影响)
＊／

[wzy_love_sly]

create table 企业销量表(etprs_id varchar(50),monthcode varchar(50),amount int)
insert into 企业销量表 select '企业1','200801',1
insert into 企业销量表 select '企业1','200802',1
insert into 企业销量表 select '企业2','200802',6

create table 月份表(monthcode varchar(50))
insert into 月份表 select '200801'
insert into 月份表 select '200802'
insert into 月份表 select '200803'


select a.etprs_id,a.monthcode,isnull( amount,0) from (
select distinct a.etprs_id,b.monthcode from 企业销量表 a ,月份表 b)a 
left join 企业销量表 b 
on a.etprs_id=b.etprs_id and a.monthcode=b.monthcode

企业1 200801 1
企业1 200802 1
企业1 200803 0
企业2 200801 0
企业2 200802 6
企业2 200803 0

[hery2002]

-->生成测试数据
 
declare @enterprise table([etprs_id] nvarchar(3),[monthcode] nvarchar(10),[amount] int)
Insert @enterprise
select N'企业1','200801',1 union all
select N'企业1','200802',1 union all
select N'企业2','200802',6
--Select * from @enterprise

declare @month table([monthcode] nvarchar(10))
Insert @month
select '200801' union all
select '200802' union all
select '200803'
--Select * from @month

select a.[etprs_id],m.[monthcode], isnull(e.[amount],0) as [amount] from (select distinct [etprs_id] from @enterprise)a
left join @month m on 1=1
left join @enterprise e on e.[monthcode] = m.[monthcode] and a.[etprs_id] = e.[etprs_id]
/*
etprs_id monthcode  amount
-------- ---------- -----------
企业1      200801     1
企业1      200802     1
企业1      200803     0
企业2      200801     0
企业2      200802     6
企业2      200803     0
*/

[pt1314917]

--> 测试数据: @企业销量表
declare @企业销量表 table (etprs_id varchar(5),monthcode int,amount int)
insert into @企业销量表
select '企业1',200801,1 union all
select '企业1',200802,1 union all
select '企业2',200802,6
--> 测试数据: @月份表
declare @月份表 table (monthcode int)
insert into @月份表
select 200801 union all
select 200802 union all
select 200803




select distinct etprs_id,b.monthcode,
amount=isnull((select amount from @企业销量表 where etprs_id=a.etprs_id and monthcode=b.monthcode),0) 
from @企业销量表 a,@月份表 b

[M1CR0S0FT]

select
a.monthcode,
b.etprs_id,
amount = case when exists(
select *
from 企业销售表
where etprs_id = b.etprs_id
and monthcode = a.monthcode) then max(amount) else 0 end
from 月份表 a,企业销售表 b
group by a.monthcode,b.etprs_id
order by etprs_id

[律己修心]

set nocount on
if object_id('tempdb.dbo.#yearmonth') is not null drop table #yearmonth
if object_id('tempdb.dbo.#sells') is not null drop table #sells
create table #yearmonth (monthcode int) 
insert into #yearmonth values(200801)
insert into #yearmonth values(200802)
insert into #yearmonth values(200803)
create table #sells (etprs_id Nvarchar(100),monthcode int,amount int)
insert into #sells select '企业1',200801,1 
union all select '企业1',200802,1 
union all select '企业2',200802,6
go
select b.etprs_id,b.monthcode,isnull(a.amount,0) as amount 
from #sells a right join
(select * from #yearmonth , (select distinct etprs_id from #sells) x) b
on a.monthcode=b.monthcode
and a.etprs_id=b.etprs_id
go
drop table #yearmonth,#sells
/*
企业1,200801,1 
企业1,200802,1 
企业1,200803,0 
企业2,200801,0 
企业2,200802,6 
企业2,200803,0 
*/

[zbc1009]

select A.etprs_id,B.MontheCode,isnull(C.amount,0) amount
from 
(select distinct etprs_id from 企业销量表) A
cross join 月份表 B 
left outer join 企业销量表 C
on A.etprs_id=C.etprs_id
and B.MonthCode=C.MonthCode
order by A.etprs_id,B.MontheCode

[fcuandy]

create table t1(etprs_id varchar(50),monthcode varchar(50),amount int)
insert into t1 select '企业1','200801',1
insert into t1 select '企业1','200802',1
insert into t1 select '企业2','200802',6
GO
create table t2(monthcode varchar(50))
insert into t2 select '200801'
insert into t2 select '200802'
insert into t2 select '200803'
GO
SELECT id,mc,ISNULL(amount,0) FROM 
(SELECT DISTINCT a.etprs_id id,b.monthcode mc
	FROM t1 a
CROSS JOIN t2 b) d
LEFT JOIN t1 c
	ON mc=monthcode AND id=etprs_id
GO
DROP TABLE t1,t2
GO

[律己修心]

7 楼正解

[让你望见影子的墙]

都很强大

[wzy_love_sly]

[Quote=引用 11 楼 GDC_ZhaoYZ0304360 的回复:]
7 楼正解
[/Quote]

哈哈，我晕，原来7楼是你写的哦

[wzy_love_sly]

偶像的和我一样 :)

[zbc1009]

偷点测试数据~~

create table #企业销量表(etprs_id varchar(50),monthcode varchar(50),amount int)
insert into #企业销量表 select '企业1','200801',1
insert into #企业销量表 select '企业1','200802',1
insert into #企业销量表 select '企业2','200802',6

create table #月份表(monthcode varchar(50))
insert into #月份表 select '200801'
insert into #月份表 select '200802'
insert into #月份表 select '200803'


select A.etprs_id,B.monthcode,isnull(C.amount,0) amount
from 
(select distinct etprs_id from #企业销量表) A
cross join #月份表 B 
left outer join #企业销量表 C
on A.etprs_id=C.etprs_id
and B.MonthCode=C.MonthCode
order by A.etprs_id,B.monthcode

[zanyzyg]

哈哈

cross 才是正解吧

小梁速度快啊

[wzy_love_sly]

[Quote=引用 15 楼 zanyzyg 的回复:]
哈哈
cross 才是正解吧
小梁速度快啊
[/Quote]

梁弟那是垃圾写法，楼主要是sql2000，一定给他0分
（我估计不是sql2000，他还是0分）

[zhang_yzy]

学习吧,都写得差不多了

[parss]

[Quote=引用 3 楼 wzy_love_sly 的回复:]
SQL codecreate table 企业销量表(etprs_id varchar(50),monthcode varchar(50),amount int)
insert into 企业销量表 select '企业1','200801',1
insert into 企业销量表 select '企业1','200802',1
insert into 企业销量表 select '企业2','200802',6

create table 月份表(monthcode varchar(50))
insert into 月份表 select '200801'
insert into 月份表 select '200802'
insert into 月份表 select '200803'


se…
[/Quote]

[randy_df]

迪卡尔乘积 + LeftJoin

SELECT a.etprs_id,
	a.monthcode,
	isnull(b.amount,0) as amount
FROM(
	SELECT distinct a.etprs_id,b.monthcode
	FROM #t1 a
	,#T2 b
) a
LEFT JOIN #t1 b
ON (a.etprs_id = b.etprs_id
AND a.monthcode = b.monthcode)

[java_303]

都求写的一样有什么意思 艾 ！
偶就不写了没意思了