一道较难面试题（mysql）

Question

mysql> select * from s1;
+----+----+------------+------------+
| t1 | t2 | t3         | t4         |
+----+----+------------+------------+
|  1 | A  | 2006-11-01 | 2006-11-03 |
|  2 | C  | 2006-11-02 | 2006-11-03 |
|  3 | B  | 2006-11-01 | 2006-11-04 |
|  4 | A  | 2006-11-03 | 2006-11-04 |
|  5 | C  | 2006-11-01 | 2006-11-02 |
|  6 | B  | 2006-11-02 | 2006-11-05 |
|  7 | A  | 2006-11-02 | 2006-11-03 |
|  8 | A  | 2006-11-04 | 2006-11-05 |
|  9 | C  | 2006-11-03 | 2006-11-04 |
| 10 | C  | 2006-11-02 | 2006-11-04 |
+----+----+------------+------------+
怎么按照t2，时间，group——concat（t1）
即模式
t2 adate            value
A  2006-11-01          1
A   2006-11-02        1,7
这是这道题的一部分：
他们公司网站上的广告位是轮播的，每天某一广告位最多可轮播的广告数量是有限制的，比如A广告位，每天只能轮播三个广告，但销售人员在销售广告位时并不考虑此限制，要求查询出合同表中，超过广告位轮播数量的合同。
mysql> select * from s1;
+----+----+------------+------------+
| t1 | t2 | t3         | t4         |
+----+----+------------+------------+
|  1 | A  | 2006-11-01 | 2006-11-03 |
|  2 | C  | 2006-11-02 | 2006-11-03 |
|  3 | B  | 2006-11-01 | 2006-11-04 |
|  4 | A  | 2006-11-03 | 2006-11-04 |
|  5 | C  | 2006-11-01 | 2006-11-02 |
|  6 | B  | 2006-11-02 | 2006-11-05 |
|  7 | A  | 2006-11-02 | 2006-11-03 |
|  8 | A  | 2006-11-04 | 2006-11-05 |
|  9 | C  | 2006-11-03 | 2006-11-04 |
| 10 | C  | 2006-11-02 | 2006-11-04 |
+----+----+------------+------------+
mysql> select * from s2;
+----+----+
| t6 | t5 |
+----+----+
| A  |  2 |
| B  |  1 |
| C  |  3 |
+----+----+
8 rows in s
广告位A每天最多可轮播2个广告，但合同表中在2006-11-03这天有三个广告（1、4、7），对于广告位A，1、4、7则是最终需要得到的结果。如需要可使用临时表、存储过程等。
最终得到
超过相应t5的值
t2 adate            value
A  2006-11-01          1
A   2006-11-02        1,7

Answer 1

建立一个日历表 calendar 放上一段期间内的日期mysql> select * from s1;
+----+------+------------+------------+
| t1 | t2   | t3         | t4         |
+----+------+------------+------------+
|  1 | A    | 2006-11-01 | 2006-11-03 |
|  2 | C    | 2006-11-02 | 2006-11-03 |
|  3 | B    | 2006-11-01 | 2006-11-04 |
|  4 | A    | 2006-11-03 | 2006-11-04 |
|  5 | C    | 2006-11-01 | 2006-11-02 |
|  6 | B    | 2006-11-02 | 2006-11-05 |
|  7 | A    | 2006-11-02 | 2006-11-03 |
|  8 | A    | 2006-11-04 | 2006-11-05 |
|  9 | C    | 2006-11-03 | 2006-11-04 |
| 10 | C    | 2006-11-02 | 2006-11-04 |
+----+------+------------+------------+
10 rows in set (0.00 sec)mysql> create table calendar(d date primary key);
Query OK, 0 rows affected (0.08 sec)mysql> insert into calendar values
    -> ('2006-11-01'),
    -> ('2006-11-02'),
    -> ('2006-11-03'),
    -> ('2006-11-04'),
    -> ('2006-11-05'),
    -> ('2006-11-06'),
    -> ('2006-11-07'),
    -> ('2006-11-08'),
    -> ('2006-11-09'),
    -> ('2006-11-10'),
    -> ('2006-11-11'),
    -> ('2006-11-12'),
    -> ('2006-11-13'),
    -> ('2006-11-14'),
    -> ('2006-11-15'),
    -> ('2006-11-16'),
    -> ('2006-11-17'),
    -> ('2006-11-18'),
    -> ('2006-11-19'),
    -> ('2006-11-20');
Query OK, 20 rows affected (0.06 sec)
Records: 20  Duplicates: 0  Warnings: 0mysql>
mysql> select s1.t2,c.d,GROUP_CONCAT(s1.t1)
    -> from calendar c ,s1
    -> where c.d between s1.t3 and s1.t4
    -> group by s1.t2,c.d;
+------+------------+---------------------+
| t2   | d          | GROUP_CONCAT(s1.t1) |
+------+------------+---------------------+
| A    | 2006-11-01 | 1                   |
| A    | 2006-11-02 | 7,1                 |
| A    | 2006-11-03 | 7,1,4               |
| A    | 2006-11-04 | 8,4                 |
| A    | 2006-11-05 | 8                   |
| B    | 2006-11-01 | 3                   |
| B    | 2006-11-02 | 6,3                 |
| B    | 2006-11-03 | 6,3                 |
| B    | 2006-11-04 | 6,3                 |
| B    | 2006-11-05 | 6                   |
| C    | 2006-11-01 | 5                   |
| C    | 2006-11-02 | 5,10,2              |
| C    | 2006-11-03 | 9,10,2              |
| C    | 2006-11-04 | 9,10                |
+------+------------+---------------------+
14 rows in set (0.00 sec)mysql>

Answer 2

照着你的方法我调出来了
 select * from 
( select s1.t2 as t2 ,c.d as d,GROUP_CONCAT(s1.t1 order by s1.t1) as g
from calendar c ,s1
where c.d between s1.t3 and s1.t4
group by s1.t2,c.d ) t
where (length(g)-length(replace(g,',',''))+1)>(select t5 from s2 where t6=t.t2);
+----+------------+-------+
| t2 | d          | g     |
+----+------------+-------+
| A  | 2006-11-03 | 1,4,7 |
| B  | 2006-11-02 | 3,6   |
| B  | 2006-11-03 | 3,6   |
| B  | 2006-11-04 | 3,6   |
+----+------------+-------+
不用中间表，能不能做出来？？？十分感谢！！！

Answer 3

不用中间表，你就直接union一个日期表 来代替。mysql> select '2006-11-01' as d
    -> union all
    -> select '2006-11-02'
    -> union all
    -> select '2006-11-03'
    -> union all
    -> select '2006-11-04'
    -> union all
    -> select '2006-11-05';
+------------+
| d          |
+------------+
| 2006-11-01 |
| 2006-11-02 |
| 2006-11-03 |
| 2006-11-04 |
| 2006-11-05 |
+------------+
5 rows in set (0.00 sec)mysql>

Answer 4

update 更新可以用变量么？？
create table TempTable(ID int,VendorID varchar(20),YearMonth int,BeginData int,Debit int,Credit int,EndData int)
Insert Into TempTable(ID,VendorID,YearMonth,BeginData,Debit,Credit,EndData)
Select 1,'001',200801,100,50,30,120
Union All Select 2,'001',200802,0,200,300,0
Union All Select 3,'001',200803,0,30,10,0
Union All Select 4,'002',200801,200,300,100,400
Union All Select 5,'002',200802,0,200,300,0
Union All Select 6,'002',200803,0,150,200,0
Union All Select 7,'003',200801,50,30,40,40
Union All Select 8,'003',200802,0,10,60,0
Union All Select 9,'003',200803,0,10,40,0
select * from @TempTable
/*算法：期初余额(BeginData)=同一个VendorID上条记录.期末余额(EndData)   
        期末余额(EndData)=期初(BeginData)+加项(Debit)-减项(Credit)
这不是那天我写的，大概是这样的：
create procedure spupdate()
begin
update TempTable
set @bd=case when VendorID=@VendorID then @ed else BeginData end
    ,@ed=case when VendorID=@VendorID then @ed+Debit-Credit else EndData end
    ,@VendorID=VendorID
    ,BeginData=@bd
    ,EndData=@ed;
select * from TempTable;
end
我写好报错，我问sql群别人说好像是set 不支持变量使用，但是我纳闷存储过程怎么把我数据给改了？？？

Answer 5

同问提啊，update  set中可以使用变量么