IM数据库相关问题专用贴~欢迎提问!!
欢迎提问~谢谢各位了!求主细表主键id添加的存储过程
主表 yh：字段
y_id（主键）,y_nam
细表infomation：
i_id（主键）,y_id,i_name,i_sex
其中两个表的y_id是两个表关联的索引
我想插入主表 yh同时，细表infomation也录入相同y_id的信息。
回答:
create table A
(
y_id int IDENTITY (1,1)NOT NULL,
y_name varchar(10)
)
create table B
(
i_id int IDENTITY (1,1)NOT NULL,
y_id int,
i_nmae varchar(10),
i_sex varchar(10)
)
create proc Test_insert(@y_name varchar(10),@i_nmae varchar(10),@i_sex varchar(10))
as
declare @y_id int
insert A select @y_name
select @y_id=@@identity
insert B select @y_id,@i_nmae,@i_sex
exec Test_insert 'werw','1111','23234'
select * from A
select * from B 请教,如何得到如下显示形式的??另如果在查询结果后边加一个balance字段,计算方法是本行的LoanDate- ReturnDate加上上一行的差值
--建立表结构
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tCustomer]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tCustomer]
GO
CREATE TABLE [dbo].[tCustomer] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ContractNumber] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,--合同号主键
[CustomerName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[CustomerSex] [varchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
[CustomerDate] [datetime] NULL ,
[CustomerDesc] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tCustomerLoan]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tCustomerLoan]
GO
CREATE TABLE [dbo].[tCustomerLoan] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ContractNumber] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[CustomerID] [int] NULL ,
[Customerloan] [decimal](18, 3) NULL ,
[LoanDate] [datetime] NULL ,
[LoanDesc] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tCustomerReturn]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tCustomerReturn]
GO
CREATE TABLE [dbo].[tCustomerReturn] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ContractNumber] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[CustomerID] [int] NULL ,
[CustomerReturn] [decimal](18, 3) NULL ,
[ReturnDate] [datetime] NULL ,
[ReturnDesc] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
--插入测试数据
insert tCustomer select '10001','张三','男','2006-01-01',null
insert tCustomer select '10002','李四','男','2006-01-01',null
insert tCustomerLoan select '10001',1,18152.43,'2006-1-27',null
insert tCustomerLoan select '10001',1,18152.43,'2006-2-27',null
insert tCustomerLoan select '10001',1,18152.43,'2006-3-27',null
insert tCustomerLoan select '10001',1,18152.43,'2006-4-27',null
insert tCustomerLoan select '10001',1,18152.43,'2006-5-27',null
insert tCustomerReturn select '10001',1,8152.43,'2006-1-2',null
insert tCustomerReturn select '10001',1,8152.43,'2006-1-31',null
insert tCustomerReturn select '10001',1,8152.43,'2006-2-27',null
--我现在只能得到如下形式:
10001 张三 男 2006-01-01 00:00:00.000 18152.430 2006-01-27 00:00:00.000NULLNULL
10001 张三 男 2006-01-01 00:00:00.000 18152.430 2006-02-27 00:00:00.000NULLNULL
10001 张三 男 2006-01-01 00:00:00.000 18152.430 2006-03-27 00:00:00.000NULLNULL
10001 张三 男 2006-01-01 00:00:00.000 18152.430 2006-04-27 00:00:00.000NULLNULL
10001 张三 男 2006-01-01 00:00:00.000 18152.430 2006-05-27 00:00:00.000NULLNULL
10001 张三 男 2006-01-01 00:00:00.000 NULL NULL 8152.430 2006-01-02 00:00:00.000
10001 张三 男 2006-01-01 00:00:00.000 NULLNULL8152.4302006-01-31 00:00:00.000
10001 张三 男 2006-01-01 00:00:00.000 NULLNULL8152.4302006-02-27 00:00:00.000
10002李四男2006-01-01 00:00:00.000NULLNULLNULLNULL
10002李四男2006-01-01 00:00:00.000NULLNULLNULLNULL
--想得到如下显示结果:
'10001','张三','男','2006-01-01',NULL, Null, 8152.43,'2006-1-2'
'10001','张三','男','2006-01-01',18152.43,'2006-1-27',
'10001','张三','男','2006-01-01',NULL, Null, 8152.43,'2006-1-31
'10001','张三','男','2006-01-01',18152.43,'2006-2-27', 8152.43,'2006-2-27'
'10001','张三','男','2006-01-01',18152.43,'2006-3-27', null, Null
'10001','张三','男','2006-01-01',18152.43,'2006-4-27', null, Null
'10001','张三','男','2006-01-01',18152.43,'2006-5-27', null, Null
回答:
select id=identity(int,1,1), *
into #
from(
select top 100 percent *
from(
select a.ContractNumber, a.CustomerName, a.CustomerSex,
b.Customerloan, b.LoanDate,
CustomerReturn = cast(null as decimal(18,3)), ReturnDate=cast(null as datetime)
from tCustomer a
inner join tCustomerLoan b
on a.ContractNumber = b.ContractNumber
union all
select a.ContractNumber, a.CustomerName, a.CustomerSex,
CustomerReturn = cast(null as decimal(18,3)), ReturnDate=cast(null as datetime),
c.CustomerReturn, c.ReturnDate
from tCustomer a
inner join tCustomerReturn c
on a.ContractNumber = c.ContractNumber
)a
order by ContractNumber, isnull(LoanDate, ReturnDate)
)aa
select *, balance=(select sum(isnull(LoanDate,0))- sum(isnull(ReturnDate),0) from # where id<=a.id)
from #
drop table #
暴力破解SQL密码
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_GetPassword]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[p_GetPassword]
GO
/*--穷举法破解 SQL Server 用户密码
可以破解中文,特殊字符,字符+尾随空格的密码
为了方便显示特殊字符的密码,在显示结果中,显示了组成密码的ASCII
理论上可以破解任意位数的密码,一般的电脑都可以破解3位密码
条件是你的电脑配置足够,时间足够
--邹建 2004.08(引用请保留此信息)--*/
/*--调用示例
--测试特殊字符
declare @pwd sysname
set @pwd=char(0)+'a '
exec sp_password null,@pwd,'sa'
exec p_GetPassword
--测试带空格的密码
exec sp_password null,'a ','sa'
exec p_GetPassword
--测试中文
exec sp_password null,'我 ','sa'
exec p_GetPassword
--清除密码
exec sp_password null,null,'sa'
--*/
create proc p_GetPassword
@username sysname=null,--用户名,如果不指定,则列出所有用户
@pwdlen int=3--密码破解的位数,默认只破解3位及以下的密码
as
--生成要破解的密码的用户表
select name,password
,type=case when xstatus&2048=2048 then 1 else 0 end
,jm=case when password is null or datalength(password)<46
then 1 else 0 end
,pwdstr=case when datalength(password)<46
then cast(password as sysname)
else cast('' as sysname) end
,pwd=cast('' as varchar(8000))
into #pwd
from master.dbo.sysxlogins a
where srvid is null
and name=isnull(@username,name)
--生成临时表
select top 255 id=identity(int,0,1) into #t from sysobjects a,sysobjects b
alter table #t add constraint PK_#t primary key(id)
--清理不需要的字符
if not exists(select 1 from #pwd where type=1)
delete from #t where id between 65 and 90 or id between 129 and 254
--密码破解处理
declare @l int
declare @s1 varchar(8000),@s2 varchar(8000),@s3 varchar(8000),@s4 varchar(8000)
--破解1位密码
select @l=0
,@s1='id=a.id'
,@s2='#t a'
,@s3='char(b.id)'
,@s4='cast(b.id as varchar)'
exec('
update pwd set jm=1,pwdstr='+@s3+'
,pwd='+@s4+'
from #pwd pwd,#t b
where pwd.jm=0
and pwdcompare('+@s3+',pwd.password,pwd.type)=1
')
--破解超过2位的密码
while exists(select 1 from #pwd where jm=0 and @l<@pwdlen-1)
begin
select @l=@l+1
,@s1=@s1+',id'+cast(@l as varchar)
+'='+char(@l/26+97)+char(@l%26+97)+'.id'
,@s2=@s2+',#t '+char(@l/26+97)+char(@l%26+97)
,@s3=@s3+'+char(b.id'+cast(@l as varchar)+')'
,@s4=@s4+'+'',''+cast(b.id'+cast(@l as varchar)+' as varchar)'
exec('
select '+@s1+' into #tt from '+@s2+'
update pwd set jm=1,pwdstr='+@s3+'
,pwd='+@s4+'
from #pwd pwd,#tt b
where pwd.jm=0
and pwdcompare('+@s3+',pwd.password,pwd.type)=1
')
end
--显示破解的密码
select 用户名=name,密码=pwdstr,密码ASCII=pwd
from #pwd
go
exec p_GetPassword 表1为客户基本信息表
ID companyname addr tel fax
1 北京A xxx 1234 5678
......
表2为客户订单表
OrderID CID ProductType Product Quantity
1 1 电子 MP3 22
2 1 电子 电脑 11
3 1 生活 脸盆 11
4 1 生活 香皂 44
........
现在想通过一条SQL语句得到这样的表
ID companyname addr tel fax 电子 生活
1 北京A xxx 1234 5678 MP3X22，电脑X11 脸盆X11，香皂X44 create table a
(ID int, companyname char(10), addr char(10), tel int ,fax int)
insert a
select 1 ,'北京A' ,'xxx' ,1234 ,5678
create table b
(OrderID int, CID int, ProductType char(10), Product char(10), Quantity char(10))
insert b
select 1 ,1 ,'电子' ,'MP3' ,'22'
union all
select 2 ,1 ,'电子' ,'电脑' ,'11'
union all
select 3 ,1 ,'生活' ,'脸盆' ,'11'
union all
select 4 ,1 ,'生活' ,'香皂' ,'44'
create function cx(@producttype varchar(8000))
returns varchar(8000)
as
begin
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+','+product+'*'+quantity from b where producttype=@producttype
set @sql=stuff(@sql,1,1,'')
return @sql
end
go
select id,companyname,addr,tel,fax,cid
,电子=max(case when producttype='电子' then aa end)
,生活=max(case when producttype='生活' then aa end)
from a join (select cid,producttype,aa=dbo.cx(producttype) from b group by cid,producttype)c on a.id=c.cid
group by id,companyname,addr,tel,fax,cid
drop function cx
drop table a
drop table b
id companyname addr tel fax cid 电子 生活
----------- ----------- ---------- ----------- ----------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 北京A xxx 1234 5678 1 MP3 *22 ,电脑 *11 脸盆 *11 ,香皂 *44
（所影响的行数为 1 行） 问题:
两个表的结构如下：
一、费用类型表：
项目号 项目名称 费用类型
1 西药费 3，4，7，8
2 中草药费 5，6
3 化验费 10，11，13，14，15
......
二、费用表：
病人号 核算方法 费用类型 费用
1102 0 3 1.55
1102 0 10 3.50
1102 4 9 5.05
1103 0 4 20.58
1103 4 9 20.58
......
现在是这样：
select 西药费=sum(cast when 费用类型 in(select 费用类型 from 费用类型表 where 项目号=1 then 费用 else NULL end))，
中草药费=sum(cast when 费用类型 in(select 费用类型 from 费用类型表 where 项目号=2 then 费用 else NULL end)),
化验费=sum(cast when 费用类型 in(select 费用类型 from 费用类型表 where 项目号=3 then 费用 else NULL end))
from 费用表 where 核算方法=0
问题：一、其中：‘西药费’，‘中草药费’，‘化验费’是从费用类型表中提取的，这样的动态字段如何从表‘费用类型表’动态得到。
二、但是上面的写法是不行的，用
charindex(','+cast(费用类型 as varchar(4))+',',','+@text+',')>0 可以。
总而言之，我该如何用表“费用类型表”的‘项目名称’和‘费用类型’来查询表‘费用表’里对应项目的费用呢？ 回答:
create table #t1(项目号 int,项目名称 varchar(10),费用类型 varchar(20))
insert into #t1 select 1,'西药费' ,'3，4，7，8'
insert into #t1 select 2,'中草药费','5，6'
insert into #t1 select 3,'化验费' ,'10，11，13，14，15'
create table #t2(病人号 int,核算方法 int,费用类型 int,费用 numeric(5,2))
insert into #t2 select 1102,0,3 ,1.55
insert into #t2 select 1102,0,10,3.50
insert into #t2 select 1102,4,9 ,5.05
insert into #t2 select 1103,0,4 ,20.58
insert into #t2 select 1103,4,9 ,20.58
declare @s varchar(8000)
set @s=''
select @s=@s+','+项目名称+'=sum(case when charindex(''，''+rtrim(费用类型)+''，'',''，'+rtrim(费用类型)+'，'')>0 then 费用 else 0 end)'
from #t1 order by 项目号
set @s='select '+stuff(@s,1,1,'')+' from #t2'
exec(@s)
/*
西药费 中草药费 化验费
---------- ---------- ----------------------------------------
22.13 0.00 3.50
*/
drop table #t1,#t2 问题:
表user
id name pass sex right
int char char int int
(自动编号) 0:女生 0:有权限
1:男生 1:无权限
男生和女生重名的有多少人 select
count(distinct t.name) as 组
from
[user] t
where
exists(select 1 from [user] where name=t.name and sex!=t.sex) 问题:
表t1的数据如下：
id f1 f2 f3
1 101 0 现金
2 10101 1 人民币
3 10102 1 港币
4 10103 1 美金
5 102 0 银行存款
6 10201 0 中国银行
7 1020101 1 中国银行A支行
8 131 1 材料成本差异
说明：t1.f2为明细字段，t1.f2=1表示为明细记录，比如t1.f1=101有下一级，所以t1.id=1时的t1.f2=0，“101”的下一级有：10101和10102；t1.f1=131时，由于没有下一级，所以t1.f2=1是明细记录；以此类推。
表t2的数据如下：
id f1 f2 f3
1 10101 10 30
2 10102 30
3 10201
4 1210101 40 50
5 131 60
我想把t2的数据实现：
id f1 f2 f3 f4
1 101 40 30 现金
3 102 40 50 银行存款
5 131 60 材料成本差异 回答:
declare @t1 table(id int,f1 varchar(10),f2 int,f3 varchar(20))
insert into @t1 select 1,rtrim('101 '),0,rtrim('现金 ')
insert into @t1 select 2,rtrim('10101 '),1,rtrim('人民币 ')
insert into @t1 select 3,rtrim('10102 '),1,rtrim('港币 ')
insert into @t1 select 4,rtrim('10103 '),1,rtrim('美金 ')
insert into @t1 select 5,rtrim('102 '),0,rtrim('银行存款')
insert into @t1 select 6,rtrim('10201 '),0,rtrim('中国银行')
insert into @t1 select 7,rtrim('1020101'),1,rtrim('中国银行A支行')
insert into @t1 select 8,rtrim('131 '),1,rtrim('材料成本差异')
declare @t2 table(id int,f1 varchar(10),f2 int,f3 int)
insert into @t2 select 1,rtrim('10101 '),10 ,30
insert into @t2 select 2,rtrim('10102 '),30 ,null
insert into @t2 select 3,rtrim('10201 '),null,null
insert into @t2 select 4,rtrim('1020101'),40 ,50
insert into @t2 select 5,rtrim('131 '),null,60
select
min(b.id) as id ,
a.f1,
sum(b.f2) as f2,
sum(b.f3) as f3,
a.f3 as f4
from
@t1 a,@t2 b
where
a.f1=left(b.f1,3)
group by
a.f1,a.f3
order by
id
/*
id f1 f2 f3 f4
----------- ---------- ----------- ----------- --------------------
1 101 40 30 现金
3 102 40 50 银行存款
5 131 NULL 60 材料成本差异
*/ 问题:
比如数据如下
ID f1 f2 f3 f4 f5 f6
1 001 进仓 100 3
2 001 进仓 30 6
3 001 出仓 50
4 001 进仓 60 5
5 001 出仓 30
6 001 出仓 40 3.8
如何实现如下结果
ID f1 f2 f3 f4 f5 f6
1 001 进仓 100 3 300 100
2 001 进仓 30 6 480 130
3 001 出仓 50 3.69 295.5 80
4 001 进仓 60 5 595.5 140
5 001 出仓 30 4.25 468 110
6 001 出仓 40 3.8 313 70
说明：f1=名称，f2=进出仓，f3=数量，f4=单价，f5=结存金额，f6=结存数量
结存金额=上一次的结存金额+数量*单价(进仓)-数量*单价(出仓)，结存数量=上一次的结存数量+数量（进仓）-数量(出仓)
如何用sql语句实现，但不要用游标来实现。 declare @t table(ID int,f1 varchar(8),f2 varchar(8),f3 int,f4 numeric(5,2),f5 numeric(5,1),f6 int)
insert into @t select 1,'001','进仓',100, 3,null,null
insert into @t select 2,'001','进仓', 30, 6,null,null
insert into @t select 3,'001','出仓', 50,null,null,null
insert into @t select 4,'001','进仓', 60, 5,null,null
insert into @t select 5,'001','出仓', 30,null,null,null
insert into @t select 6,'001','出仓', 40, 3.8,null,null
declare @sum numeric(5,2),@total int,@price numeric(5,2)
set @sum =0
set @total=0
update @t
set
@sum =@sum +(case when f2='出仓' and f4 is null then -@price*f3 when f2='出仓' then -f3*f4 else f3*f4 end),
@total=@total+(case f2 when '出仓' then -f3 else f3 end),
@price=@sum/@total,
f4=(case when f2='出仓' and f4 is null then @price else f4 end),
f5=@sum,
f6=@total
select * from @t
/*
ID f1 f2 f3 f4 f5 f6
----------- -------- -------- ----------- ------- ------- -----------
1 001 进仓 100 3.00 300.0 100
2 001 进仓 30 6.00 480.0 130
3 001 出仓 50 3.69 295.5 80
4 001 进仓 60 5.00 595.5 140
5 001 出仓 30 4.25 468.0 110
6 001 出仓 40 3.80 316.0 70
*/ !:( 真厉害啊 我都看不动 (^#@^
页:
[1]
