Java开发网 - 请教一下这个 Select 查询该怎么写？

Topic: 请教一下这个 Select 查询该怎么写？

1.请教一下这个 Select 查询该怎么写？

Posted by: ant21
Posted on: 2005-09-12 23:54

好像不太复杂，可是自己又写的总觉得不太对劲，所以还是向大家求教一下好了。

有两张表：l_reader 和 l_loan

l_reader 的结构是：


-+------------+-------------+-------+--------+-------------+---------------
 |读者条码 | 读者姓名 | 性别 | 单位 | 欠款金额 |  ... 等等
-+------------+-------------+-------+--------+-------------+---------------
 |12345    | 甲          | 男    | a      | 10.00     |
-+------------+-------------+-------+--------+------------ +---------------
 |67890    | 乙          | 女    | b      | 5.00      |
-+------------+-------------+-------+--------+-------------+---------------
 |75667    | 丙          | 女    | c      | 6.00       |
-+------------+-------------+-------+--------+-------------+---------------

l_loan 的结构是：


-+------------+-------------+------------------+-----------------+---------------
 |图书条码 | 读者姓名 | 借书日期      | 应还日期     |  ... 等等
-+------------+-------------+------------------+------------------+---------------
 |79875    | 甲          | 2001-07-06  | 2002-06-21 |
-+------------+-------------+------------------+------------------+---------------
 |32165    | 乙          | 2003-07-06  | 2004-06-21 |
-+------------+-------------+------------------+------------------+--------------
 |78545    | 甲          | 2002-05-06  | 2003-05-21 |
-+------------+-------------+------------------+------------------+---------------

l_reader 中，读者条码是唯一的，l_loan 中，图书条码是唯一的

我希望的查询结果是：


-+-------------+-------------+--------------+---------------
 | 读者姓名 | 读者条码 | 未还册数 |   ... 等等
-+-------------+-------------+--------------+---------------
 | 甲          |  12345   |  2           |
-+-------------+-------------+--------------+---------------
 | 乙          |  54656   |  1           | 
-+-------------+-------------+--------------+---------------
 | 丁          |  87985   |  3           | 
-+-------------+-------------+--------------+---------------

请问大家这个查询该怎么写呢？

还有，如果我想要查出每个读者没有归还图书的所有条码，可以在一个查询里面完成吗？就是说，比如甲还有 2 本没还，那么是否可以一次查出那 2 本的图书条码，直接显示在上表的后面呢？

谢谢！

奇怪，表格怎么变得这么乱了！

2.Re:请教一下这个 Select 查询该怎么写？ [Re: ant21]

Copy to clipboard

Posted by: why
Posted on: 2005-09-13 08:21

> 请问大家这个查询该怎么写呢？
Which database do you use, sub-select statement (nested-select) may be different.
Could you create a view for this?

> 那么是否可以一次查出那 2 本的图书条码，直接显示在上表的后面呢？
Again, which database do you use?
(e.g. Oracle has Tree extension [connect by])
It is quite difficult to use a single select statement to do so.

BTW, it is not a good design to have 读者姓名 as a field (reference id) in l_loan rather than an unique id like 读者条码.

3.Re:请教一下这个 Select 查询该怎么写？ [Re: ant21]

Copy to clipboard

Posted by: ant21
Posted on: 2005-09-13 10:44

>Which database do you use?

非常感谢 why 的回复，真是抱歉，忘了说数据库用的是 MS SQL Server 2000。

>it is not a good design to have 读者姓名 as a field (reference id) in l_loan rather than an unique id like 读者条码.

嗯，why 所言极是，如果 l_loan 中用读者条码作为 reference id 就好了，可惜的是，数据库是一个桌面应用的后台，我只能对它进行查询然后在 web 上显示相关的内容。

4.Re:请教一下这个 Select 查询该怎么写？ [Re: ant21]

Copy to clipboard

Posted by: why
Posted on: 2005-09-13 11:23


select reader.[读者姓名], reader.[读者条码], isnull( loan.[未还册数], 0) as [未还册数]
from l_reader reader left outer join (
  select [读者姓名], count([读者姓名]) as [未还册数]
  from l_loan
  where [还书日期] is null -- or whatever condition to determine 未还
  group by [读者姓名] ) as loan 
    on reader.[读者姓名] = loan.[读者姓名]

- use inner join if readers with no outstanding loan are not included.
- if possible, create a view for 'select [读者姓名], count([读者姓名]) as [未还册数] ..."

It is not suggested to include 图书条码 in one select statement.
If this is really necessary, consider using a stored procedure to prepare this string of 图书条码.

5.Re:请教一下这个 Select 查询该怎么写？ [Re: ant21]	Copy to clipboard
Posted by: ant21 Posted on: 2005-09-13 13:18 非常感谢 why，您写的 query 语句我得回去好好消化一下，再次感谢您的帮助！

6.Re:请教一下这个 Select 查询该怎么写？ [Re: ant21]	Copy to clipboard
Posted by: ken0719 Posted on: 2005-09-15 17:55 Maybe there is a mistake in the database design. In the table 'l_loan', why not use '读者条码' to replace '读者姓名'? If there are some readers with same name, how to handle this case?

7.Re:请教一下这个 Select 查询该怎么写？ [Re: ant21]	Copy to clipboard
Posted by: q_yuan Posted on: 2005-09-27 09:30 [color= white] 直得学习![/color]

8.Re:请教一下这个 Select 查询该怎么写？ [Re: ant21]	Copy to clipboard
Posted by: q_yuan Posted on: 2005-09-27 09:40 <font bgcolor = #ffffff>我也学到了</font>

9.Re:请教一下这个 Select 查询该怎么写？ [Re: ken0719]

Copy to clipboard

Posted by: ant21
Posted on: 2005-09-28 13:02

ken0719 wrote:
Maybe there is a mistake in the database design. In the table 'l_loan', why not use '读者条码' to replace '读者姓名'? If there are some readers with same name, how to handle this case?

非常感谢您的提醒，后来我再次检查的时候，发现 l_loan 中确实用的"读者条码"而不是“读者姓名”，这样，参考 why 的 query，我的 query 是这样的：


select reader._读者姓名, reader._读者条码, isnull( loan.未还册数, 0) as 未还册数
from l_reader reader
right join 
  (
  select _读者条码, count(_读者条码) as 未还册数
  from l_loan
  where _应还日期 < getdate()
  group by _读者条码
  ) as loan
on reader._读者条码 = loan._读者条码
order by 未还册数 DESC

感谢大家的帮助！