面试题 写SQL语句
如题:
一、有表Sales
Region Product Qty
Beijing A 87
Beijing D 92.5
Tianjin B 86
Tianjin A 82
... ...
让做一交叉表 显示某一地区各个产品的销售量 用存储过程实现 即实现如下结构
Region A B C D ... ...
Beijing 87 55 58 92.5 ... ...
Tianjin 82 86 55 57 ... ...
... ...
二、有表A(fa,fb,fc,fd)和B(fa,fb,fc,fd) 令其连接(A.fa=B.fa and A.fb=B.fb) 通过更新A表的fc更新B表的fc 写出SQL语句 我想
了半天 写成了这样:
update A,B
set B.fc=newValue
where A.fa=B.fa and A.fb=B.fb
呵呵 肯定是把人丢在外边了 我想它实际是想让写一个触发器来着 更新A表然后通过触发器更新B表
诸位帮看下
[解决办法]
--参考
/*
建立日期：2006-12-29
参考网址：http://community.csdn.net/Expert/topic/5259/5259689.xml?temp=.4286768
功能描述：
行列转换(将表旋转90度)
表T：
姓名语文数学物理
----------------------------------
张三809085
李四859282
要求结果：
课程张三李四
----------------------
语文8085
数学9092
物理8582
*/
--创建环境
CREATE TABLE T(姓名 nvarchar(10), 语文 int, 数学 int, 物理 int)
INSERT T SELECT '张三 ',80,90,85
UNION ALL SELECT '李四 ',85,92,82
--SQL
--步骤一：生成中间数据表
DECLARE @sql varchar(8000)
SET @sql = 'CREATE TABLE T2(课程 nvarchar(10) '
SELECT @sql=@sql+ ', '+姓名+ ' nvarchar(10) ' FROM T
SET @sql=@sql+ ') '
EXEC(@sql)
--PRINT @sql
--打印结果：CREATE TABLE T2(课程 nvarchar(10),张三 nvarchar(10),李四 nvarchar(10))
--步骤二：借助中间数据表实现行列转换
DECLARE @name nvarchar(10)
DECLARE T_cursor CURSOR FOR
SELECT name FROM syscolumns
WHERE id=OBJECT_ID( 'T ') and colid> 1
ORDER BY colid
OPEN T_cursor
FETCH NEXT FROM T_cursor INTO @name
WHILE @@FETCH_STATUS=0
BEGIN
EXEC( 'SELECT '+@name+ ' AS T INTO T3 FROM T ')
SET @sql= 'INSERT INTO T2 SELECT ' ' '+@name+ ' ' ' '
SELECT @sql=@sql+ ', ' ' '+RTRIM(T)+ ' ' ' ' FROM T3
EXEC(@SQL)
EXEC( 'DROP TABLE T3 ')
FETCH NEXT FROM T_cursor INTO @name
END
CLOSE T_cursor
DEALLOCATE T_cursor
--查看处理结果
SELECT * FROM T
SELECT * FROM T2
--删除环境
DROP TABLE T, T2
[解决办法]
http://topic.csdn.net/t/20061227/15/5259689.html
此贴作者也给出了另一种思路
可以参考下
[解决办法]
------question 1
/*Region Product Qty
Beijing A 87
Beijing D 92.5
Tianjin B 86
Tianjin A 82
*/
create table commodity
(
region varchar(1000) not null,
product char(1) not null,
qty int not null
)
insert into commodity
select 'beijing ', 'A ',87 union all
select 'beijing ', 'D ',92 union all
select 'tianjin ', 'B ',82 union all
select 'tianjin ', 'A ',34
declare @sql varchar(8000)
set @sql= ' ';
select @sql=@sql+ ',sum(case product when ' ' '+product+ ' ' ' then qty else 0 end) as [ '+product+ '] ' from (select distinct product from commodity) a
set @sql= 'select region '+@sql+ ' from commodity group by region '
exec(@sql)
[解决办法]
if object_id( 'testproc ') is not null
drop proc testproc
create table commodity
(
region varchar(1000) not null,
product char(1) not null,
qty int not null
)
insert into commodity
select 'beijing ', 'A ',87 union all
select 'beijing ', 'D ',92 union all
select 'tianjin ', 'B ',82 union all
select 'tianjin ', 'A ',34
drop proc testproc
create proc testproc
as
begin
declare @sql varchar(8000)
set @sql= ' ';
select @sql=@sql+ ',sum(case product when ' ' '+product+ ' ' ' then qty else 0 end) as [ '+product+ '] ' from (select distinct product from commodity) a
set @sql= 'select region '+@sql+ ' from commodity group by region '
exec(@sql)
end
exec dbo.testproc
这不就是存储过程么....
