我有两个表:1.商品大类表t_spda,主要字段:大类编号dlbh,比如有三个大类5000,,5001,5002。
2.销售类别表t_xslb,主要字段:销售类别xslb(比如有三类1,2,3),类别折扣率lbzk(比如对应为0.7,0.8,0.9)
我要写的sql语句是表t_spda的每一条记录对应表t_xslb的所用记录,即具体显示如下:
5000 1 0.7
5000 2 0.8
5000 3 0.9
5001 1 0.7
5001 2 0.8
5001 3 0.9
5002 1 0.7
5002 2 0.8
5002 3 0.9
谢谢
2.销售类别表t_xslb,主要字段:销售类别xslb(比如有三类1,2,3),类别折扣率lbzk(比如对应为0.7,0.8,0.9)
我要写的sql语句是表t_spda的每一条记录对应表t_xslb的所用记录,即具体显示如下:
5000 1 0.7
5000 2 0.8
5000 3 0.9
5001 1 0.7
5001 2 0.8
5001 3 0.9
5002 1 0.7
5002 2 0.8
5002 3 0.9
谢谢
create table a(id int,ic int)
insert into a
select 1,5000 union all
select 2,5001
gocreate table b(ig decimal(6,2))
insert into b
select 6.1 union all
select 6.11
go
--1
select *
from a,b
--2
select *
from a cross join bdrop table a,b/*
id ic ig
----------- ----------- ---------------------------------------
1 5000 6.10
2 5001 6.10
1 5000 6.11
2 5001 6.11(4 行受影响)id ic ig
----------- ----------- ---------------------------------------
1 5000 6.10
2 5001 6.10
1 5000 6.11
2 5001 6.11(4 行受影响)
create table t_xslb(xslb int,lbzk float)
insert into t_spda
select 5000 union all
select 5001 union all
select 5002insert into t_xslb
select 1,0.7 union all
select 2,0.8 union all
select 3,0.9select * from t_spda,t_xslb order by dlbhdrop table t_spda,t_xslb/*
5000 1 0.7
5000 2 0.8
5000 3 0.9
5001 1 0.7
5001 2 0.8
5001 3 0.9
5002 1 0.7
5002 2 0.8
5002 3 0.9
*/
(SELECT dlbh FROM t_spda) a
CROSS APPLY
(SELECT xslb, lbzk FROM t_xslb) b
select m.dlbh , n.xslb from t_spda m , t_xslb n
insert into @tb2
select 1,0.7 union all
select 2,0.8 union all
select 3,0.9
declare @tb1 table(dlbn int)
insert into @tb1
select 5000 union all
select 5001 union all
select 5002
select * from @tb1 , @tb2
use tempdb;
/*
create table t_spda
(
dlbh int not null
);
insert into t_spda(dlbh) values(5000),(5001),(5002);create table t_xslb
(
xslb int not null,
lbzk decimal(10,1) not null
);
insert into t_xslb(xslb,lbzk) values(1,0.7),(2,0.8),(3,0.9);
*/
select *
from t_spda as t1
cross join t_xslb as t2
order by t1.dlbh;