所以,觉得有必要记录下来,这样以后再次碰到这类问题,也能从中获取解答的思路。
http://bbs.csdn.net/topics/390633551
最近在做一个OA系统,用SQLServer2008数据库。系统里有个删除员工的功能,但是考虑到其他表中有员工表的外键,删除时应该将其他表中相关的记录全部删除,请问如何写语句?
比如员工表是OA_User,员工编号Uid,数据库中最少有10张表里有Uid外键,请问如何删除其他表中的相关记录?
create table OA_User(Uid int primary key ,uname varchar(20))
create table OA_tb
(
id int identity(1,1)primary key ,
Uid int foreign key references OA_User(Uid) on delete cascade --级联删除
)
insert into OA_User
values(1,'张三'),
(2,'李四')
insert into OA_tb
values(1),(1),(1),(2),(1)
--删除主表中udi 为2的记录,没有报错
delete from oa_user where uid = 2
--附表中的uid为2的记录,自动删除
select * from oa_tb
/*
id Uid
1 1
2 1
3 1
5 1
*/
也可以通过图形化操作来实现:
2、统一改换查询出的字段。。这是不是想多了?
http://bbs.csdn.net/topics/390610092
能不能这样
select A.* as A_*
from QAQuestion Q
inner join QAAnswer A ON A.QuestionID = Q.ID
简单地说,不想一个个地去给每个字段as别名
我如上去写只是打个比方。。实际运行不了的,想得到的查询结果是
A_字段1,A_字段2,A_字段3,A_字段4
有没有办法呢?
我的回复:
本质上来说,只有在sql server端,能把select a.* as a_*,也就是自动进行转换,才能支持。
因为在sql server端,你写的sql语句是各式各样的,要想实现你的A.* as A_*,实际上就是要改写查询,改为:
select a.字段1 as a_字段1,
a.字段2 as a_字段2,
a.字段3 as a_字段3,
from a
下面是通过动态语句来实现的:
--先建个表
select * into wc_table
from sys.objects
/*
要实现
select a.* as a_*
from wc_table
的效果
*/
--动态生成语句为:
declare @sql varchar(max);
set @sql = '';
select @sql = @sql + ',' + c.name + ' as A_' + c.name
from sys.tables t
inner join sys.columns c
on t.object_id = c.object_id
where t.name = 'wc_table'
order by c.column_id
set @sql = 'select ' +
STUFF(@sql,1,1,'') +
' from wc_table A'
select @sql
/*
我把结果格式化了一下就是这样:
SELECT name AS A_name,
object_id AS A_object_id,
principal_id AS A_principal_id,
schema_id AS A_schema_id,
parent_object_id AS A_parent_object_id,
type AS A_type,
type_desc AS A_type_desc,
create_date AS A_create_date,
modify_date AS A_modify_date,
is_ms_shipped AS A_is_ms_shipped,
is_published AS A_is_published,
is_schema_published AS A_is_schema_published
FROM wc_table A
*/
exec(@sql)