新手请教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之类的语法吧，哎我真没想到会出这么难的题，可能对于高手来讲就是小菜一碟但我就是过不去语法使用上的坎啊。
...全文
221 10 打赏 收藏 转发到动态 举报
写回复
用AI写文章
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
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
至於哪種更優，尚未驗證，我會選擇第二種作爲我的答案
LongRui888 2014-05-28
  • 打赏
  • 举报
 回复
上面的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就成了啊
sql面试题大全
sql 面试题 大全 JSP 开发
最全的IT公司面试题集 CHM版的
数据库面试题,英语面试,外企面试,软件测试面试题,Python面试题,Oracle面试题,MySql面试题,Web开发面试题,Unix面试题,程序员面试,网络技术面试题,网络安全面试题,Linux面试题,Hibernate面试题,Spring面试题,SQL ...
面试 SQL整理 常见的SQL面试题：经典50题
常见的SQL面试题：经典50题 三、50道面试题 2.汇总统计分组分析 3.复杂查询 sql面试题：topN问题 4.多表查询 【面试题类型总结】这类题目属于行列如何互换，解题思路如下： 其他面试题: SQL基础知识整理...
SQL数据库面试题以及答案（50例题）
SQL数据库面试题以及答案（50例题）
SQL常见的一些面试题(太有用啦)
SQL常见面试题 1.用一条SQL 语句 查询出每门课都大于80 分的学生姓名 name kecheng fenshu 张三 语文 81 张三 数学 75 李四 语文 76 李四 数学 90 王五 语文 81 王五 数学 100 王五 ...
疑难问题

22,209

社区成员

121,731

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章