求一则常用sql

Question

原始表：
--------------------------
企业销量表：
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

Answer 1

--> 测试数据: #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 行受影响)
＊／

Answer 2

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',6create 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

Answer 3

-->生成测试数据
 
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 @enterprisedeclare @month table([monthcode] nvarchar(10))
Insert @month
select '200801' union all
select '200802' union all
select '200803'
--Select * from @monthselect 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
*/

Answer 4

--> 测试数据: @企业销量表
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

Answer 5

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

Answer 6

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 
*/

Answer 7

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

Answer 8

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

Answer 9

偷点测试数据~~
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',6create 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

Answer 10

哈哈cross 才是正解吧小梁速度快啊

Answer 11

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

Answer 12

迪卡尔乘积 + LeftJoinSELECT 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)