如何查询相同select语句的每一行 [英] how to query on each row of same select statement

问题描述

Hello Experts，



我有一个简单的问题但令人困惑（对我而言）......



我在桌子上拿到一个选择语句之后有一个表格结果如下

Hello Experts,

I have simple question yet confusing(for me)...

I have a table which results after grabbing a select statement on that table are as follows

select * from VINBINI where  BABLOC='AE084'

BAITEM	               BABLOC	       BAQOH
DU266004817         	AE084     	7
DU266027621         	AE084     	3
DU266029704         	AE084     	4
DU290124118         	AE084     	36
GY407478374         	AE084     	34
GY766723358         	AE084     	0
KU1774913           	AE084     	5
KU1803313           	AE084     	0
KU1905013           	AE084     	0
KU1905213           	AE084     	0
KU1908713           	AE084     	0
KU1915313           	AE084     	7
KU2100663           	AE084     	0
KU2103303           	AE084     	0
KU2137223           	AE084     	0
KU2161003           	AE084     	0
KU2170103           	AE084     	0
KU2176683           	AE084     	6

我有另一个查询返回结果集的地方说

I have another query where it returns a result set say

Select * from TABLE1

现在有什么办法可以调用第一个选择语句以了解同一个BABLOC中是否有多个BAITEM

含义AE084在此处包含不同的项目。像DU，GY，KU等......

现在我想添加类似

1）

Now is there any way I can call the first select statement to know if there are multiple BAITEM(s) in same BABLOC
Meaning AE084 contains different Items here. like DU, GY,KU etc...
Now I want to add something like
1)

  BABLOC SHARED_BIN
  AE084    "YES"
  AE124     "NO"
  AF158     "YES"

So on...

2）

这应该是一个必须最终重新设置来自TABLE1查询的select *的值（这里放置非常大的文本）



有谁可以帮我这个吗？

2)
This should be a value that must be ultimately in reusltset of the select * from TABLE1 query (very huge text here to place)

Can anyone help me with this please?

推荐答案

我不知道DB2，但在MySql中你可以做这样的事情：

I don't know DB2, but in MySql you can do seomething like this:

select babloc,
if(
  (select count(distinct left(BAITEM,2)) from VINBINI i where i.BABLOC = e.BABLOC) > 1,
  "yes",
  "no"
) as SHARED_BIN
from VINBINI e
group by BABLOC

请参阅： http://sqlfiddle.com/#!9/21716/8 [ ^ ]



我真的不明白主题2.但如果我看对了，你可以简单地创建一个视图 [ ^ ]在此查询之上。如果常规视图需要花费太多时间来重新计算，您可以使用物化视图方法（请参阅： http://www.fromdual.ch / mysql-materialized-views [ ^ ] ）

See: http://sqlfiddle.com/#!9/21716/8[^]

I don't really understand topic 2. But if I see it right, you can simply create a view[^] on top of this query. If regular view would take too much to recalculate, you can use materialized view approach (see: http://www.fromdual.ch/mysql-materialized-views[^])

你没有提供没有共享bin的BABLOC示例，所以后面的任何内容都只是猜测。

You didn't provide an example of a BABLOC that doesn't have a shared bin, so anything that follow is just a guess.

select BABLOC, Shared_Bin = case when BinUse > 1 then "Yes" else "No"
from (
select BABLOC, BinUse = count(BAITEM) from VINBINI
group by BABLOC) dt

这篇关于如何查询相同select语句的每一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！