新手请教SQL面试题,百思不得其解啊

bigcjhk 2014-05-27 11:35:27
去面试分析师工作时候给的一道SQL题,但我的SQL水平实在不怎么样,后来拿回家来研究还是不得其解。请高手指点:



题目是:
1. 通过SQL语句找出图表1中哪(个)些交易Invoice_No下的商品的销售记录相对于折扣是错误的?
2. 通过SQL语句找出图表2中哪(个)些交易Invoice_No下的商品与销售记录相对于折扣是错误的?
3. 你认为图表1 和图表2谁在数据查询效率和处理速度上更优化?


PS.个人认为难点主要是在交易日下,不同的商品的折扣是不同的,即在2011-1-1日商品1001有8折,但其他两个商品没有折扣,所以在选择的时候要加入到折扣日期限制然后用CASE之类的语法吧,哎我真没想到会出这么难的题,可能对于高手来讲就是小菜一碟但我就是过不去语法使用上的坎啊。
...全文
194 点赞 收藏 10
写回复
10 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
bigcjhk 2014-05-29
谢谢大神,顺便分享一下我这边用的办法: select distinct h.Invoice_No from D_Item b,( select D.item_no,d.Invoice_No,d.amount,A.Discount_Percentage from SalesDetail D left join F_Discount A on D.item_no=A.item_id and D.Invoice_Date between A.Discount_Period_Start and A. Discount_Period_End ) h where b.item_id=h.item_no and (b.Unit_Price*(1-isnull(h.Discount_Percentage,0)))<>h.Amount
回复
starseeker7 2014-05-28
引用 4 楼 bigcjhk 的回复:
[quote=引用 3 楼 starseeker7 的回复:] 一般吧,实际应用中不算常见的问题,使用SQL比较多的话迟早都会碰到 给一个比较常见的日期问题解决办法 select inv_no, (case when inv_date is null then 0 else dis_per end ) as person from f_salemaster as A with(nolock) left join f_dis as B on a.inv_date bewteen b.dis_start and b.dis_end 这样你就有inv_no与person 的对照表了,再join一下d_item算出正确值对比saledetail就成了啊
谢谢, 但是case when inv_date is null then 0 else dis_per end好像不对吧, 一个是DATE,怎么能转化成0?[/quote] 你理解错了,我这个case输出的是person。也就是discount_percentage,就是折扣百分比,inv_date只是个判定标准 因为left join下,不在对应日期中的销售商品折扣百分比会是null。
回复
q530247862 2014-05-28
引用 8 楼 yoan2014 的回复:
if exists(select name from sys.objects where name = 'F_Discount')
	drop table F_Discount
go
create table F_Discount( item_id char(4) , Discount_Period_Start date , Discount_Period_End date , Discount_Percentage decimal(2,2))
go
insert into F_Discount
select '1001' , '2011-01-01' , '2011-01-05' , 0.2 union all
select '1002' , '2011-01-15' , '2011-01-20' , 0.1union all
select '1003' , '2011-02-01' , '2011-02-05' , 0.1

go
if exists(select name from sys.objects where name = 'F_SalesDetail')
	drop table F_SalesDetail
go
create table F_SalesDetail(Invoice_No char(7) , Invoice_Date date , Item_No char(4) , Amount decimal(6,2))
go
insert into F_SalesDetail
select 'A201101' , '2011-01-01' , '1001' , 64 UNION ALL 
select 'A201101' , '2011-01-01' , '1002' , 60 UNION ALL
select 'A201102' , '2011-01-10' , '1001' , 64 UNION ALL
select 'A201102' , '2011-01-10' , '1002' , 54 UNION ALL
select 'A201102' , '2011-01-10' , '1003' , 27
GO

if exists(select name from sys.objects where name = 'D_Item')
	drop table D_Item
go
create table D_Item(Item_ID char(4) , Unit_Price decimal(6,2))
go
insert into D_Item
select '1001' , 80 union all 
select '1002' , 60 union all
select '1003' , 30
go

select fs.* , 
(1 - case when fd.Discount_Percentage is null then 0 else fd.Discount_Percentage end) * di.Unit_Price 
from F_SalesDetail as fs inner join D_Item as di on  fs.item_no = di.item_ID
left join F_Discount as fd on fs.item_no = fd.item_id and fs.Invoice_Date between fd.Discount_Period_Start and fd.Discount_Period_End 
where fs.Amount <> (1 - case when fd.Discount_Percentage is null then 0 else fd.Discount_Percentage end) * di.Unit_Price 
/*
Invoice_No Invoice_Date Item_No Amount                                  
---------- ------------ ------- --------------------------------------- ---------------------------------------
A201102    2011-01-10   1001    64.00                                   80.0000
A201102    2011-01-10   1002    54.00                                   60.0000
A201102    2011-01-10   1003    27.00                                   30.0000

(3 個資料列受到影響)*/
if exists(select name from sys.objects where name = 'F_Discount_2')
	drop table F_Discount_2
go
create table F_Discount_2( item_id char(4) ,Discount_Date date , Discount_Percentage decimal(2,2))
go
insert into F_Discount_2
select '1001' , '2011-01-01' , 0.2 union all
select '1001' , '2011-01-02' , 0.2 union all
select '1001' , '2011-01-03' , 0.2 union all
select '1001' , '2011-01-04' , 0.2 union all
select '1001' , '2011-01-05' , 0.2 union all
select '1002' , '2011-01-15' , 0.1union all
select '1002' , '2011-01-16' , 0.1union all
select '1002' , '2011-01-17' , 0.1union all
select '1002' , '2011-01-18' , 0.1union all
select '1002' , '2011-01-19' , 0.1union all
select '1002' , '2011-01-20' , 0.1union all
select '1003' , '2011-02-01'  , 0.1 union all
select '1003' , '2011-02-02'  , 0.1 union all
select '1003' , '2011-02-03'  , 0.1 union all
select '1003' , '2011-02-04'  , 0.1 union all
select '1003' , '2011-02-05'  , 0.1

go


select fs.* , 
(1 - case when fd.Discount_Percentage is null then 0 else fd.Discount_Percentage end) * di.Unit_Price 
from F_SalesDetail as fs inner join D_Item as di on  fs.item_no = di.item_ID
left join F_Discount_2 as fd on fs.item_no = fd.item_id and fs.Invoice_Date = fd.Discount_Date
where fs.Amount <> (1 - case when fd.Discount_Percentage is null then 0 else fd.Discount_Percentage end) * di.Unit_Price 
至於哪種更優,尚未驗證,我會選擇第二種作爲我的答案
在不在 加下我QQ把 我有个问题,,数据库怎么都打不开帮帮我把 我都着急了一下午了· QQ530247862
回复
yoan2014 2014-05-28
if exists(select name from sys.objects where name = 'F_Discount')
	drop table F_Discount
go
create table F_Discount( item_id char(4) , Discount_Period_Start date , Discount_Period_End date , Discount_Percentage decimal(2,2))
go
insert into F_Discount
select '1001' , '2011-01-01' , '2011-01-05' , 0.2 union all
select '1002' , '2011-01-15' , '2011-01-20' , 0.1union all
select '1003' , '2011-02-01' , '2011-02-05' , 0.1

go
if exists(select name from sys.objects where name = 'F_SalesDetail')
	drop table F_SalesDetail
go
create table F_SalesDetail(Invoice_No char(7) , Invoice_Date date , Item_No char(4) , Amount decimal(6,2))
go
insert into F_SalesDetail
select 'A201101' , '2011-01-01' , '1001' , 64 UNION ALL 
select 'A201101' , '2011-01-01' , '1002' , 60 UNION ALL
select 'A201102' , '2011-01-10' , '1001' , 64 UNION ALL
select 'A201102' , '2011-01-10' , '1002' , 54 UNION ALL
select 'A201102' , '2011-01-10' , '1003' , 27
GO

if exists(select name from sys.objects where name = 'D_Item')
	drop table D_Item
go
create table D_Item(Item_ID char(4) , Unit_Price decimal(6,2))
go
insert into D_Item
select '1001' , 80 union all 
select '1002' , 60 union all
select '1003' , 30
go

select fs.* , 
(1 - case when fd.Discount_Percentage is null then 0 else fd.Discount_Percentage end) * di.Unit_Price 
from F_SalesDetail as fs inner join D_Item as di on  fs.item_no = di.item_ID
left join F_Discount as fd on fs.item_no = fd.item_id and fs.Invoice_Date between fd.Discount_Period_Start and fd.Discount_Period_End 
where fs.Amount <> (1 - case when fd.Discount_Percentage is null then 0 else fd.Discount_Percentage end) * di.Unit_Price 
/*
Invoice_No Invoice_Date Item_No Amount                                  
---------- ------------ ------- --------------------------------------- ---------------------------------------
A201102    2011-01-10   1001    64.00                                   80.0000
A201102    2011-01-10   1002    54.00                                   60.0000
A201102    2011-01-10   1003    27.00                                   30.0000

(3 個資料列受到影響)*/
if exists(select name from sys.objects where name = 'F_Discount_2')
	drop table F_Discount_2
go
create table F_Discount_2( item_id char(4) ,Discount_Date date , Discount_Percentage decimal(2,2))
go
insert into F_Discount_2
select '1001' , '2011-01-01' , 0.2 union all
select '1001' , '2011-01-02' , 0.2 union all
select '1001' , '2011-01-03' , 0.2 union all
select '1001' , '2011-01-04' , 0.2 union all
select '1001' , '2011-01-05' , 0.2 union all
select '1002' , '2011-01-15' , 0.1union all
select '1002' , '2011-01-16' , 0.1union all
select '1002' , '2011-01-17' , 0.1union all
select '1002' , '2011-01-18' , 0.1union all
select '1002' , '2011-01-19' , 0.1union all
select '1002' , '2011-01-20' , 0.1union all
select '1003' , '2011-02-01'  , 0.1 union all
select '1003' , '2011-02-02'  , 0.1 union all
select '1003' , '2011-02-03'  , 0.1 union all
select '1003' , '2011-02-04'  , 0.1 union all
select '1003' , '2011-02-05'  , 0.1

go


select fs.* , 
(1 - case when fd.Discount_Percentage is null then 0 else fd.Discount_Percentage end) * di.Unit_Price 
from F_SalesDetail as fs inner join D_Item as di on  fs.item_no = di.item_ID
left join F_Discount_2 as fd on fs.item_no = fd.item_id and fs.Invoice_Date = fd.Discount_Date
where fs.Amount <> (1 - case when fd.Discount_Percentage is null then 0 else fd.Discount_Percentage end) * di.Unit_Price 
至於哪種更優,尚未驗證,我會選擇第二種作爲我的答案
回复
上面的case 应该是可以的
回复
bigcjhk 2014-05-27
这张?
回复
AlphaQCode 2014-05-27
题目不难,但实在看不清...
回复
guguda2008 2014-05-27
mark一下,等有空了写语句解
回复
bigcjhk 2014-05-27
引用 3 楼 starseeker7 的回复:
一般吧,实际应用中不算常见的问题,使用SQL比较多的话迟早都会碰到 给一个比较常见的日期问题解决办法 select inv_no, (case when inv_date is null then 0 else dis_per end ) as person from f_salemaster as A with(nolock) left join f_dis as B on a.inv_date bewteen b.dis_start and b.dis_end 这样你就有inv_no与person 的对照表了,再join一下d_item算出正确值对比saledetail就成了啊
谢谢, 但是case when inv_date is null then 0 else dis_per end好像不对吧, 一个是DATE,怎么能转化成0?
回复
starseeker7 2014-05-27
一般吧,实际应用中不算常见的问题,使用SQL比较多的话迟早都会碰到 给一个比较常见的日期问题解决办法 select inv_no, (case when inv_date is null then 0 else dis_per end ) as person from f_salemaster as A with(nolock) left join f_dis as B on a.inv_date bewteen b.dis_start and b.dis_end 这样你就有inv_no与person 的对照表了,再join一下d_item算出正确值对比saledetail就成了啊
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2014-05-27 11:35
社区公告
暂无公告