gpt4 book ai didi

mysql - 两张表的sql sum count(*)

转载 作者:行者123 更新时间:2023-11-30 22:19:45 24 4
gpt4 key购买 nike

我有两个表 w300v600 我想计算机器的数量。问题是只计算与表字段 v600.vb 组合的机器。

Table v600 fields: vb, konto, satzart, fi_nr 

Table w300 fields: maschnr, konto, satzart, fi_nr,

fi_nr[Key]
konto[Key]
satzart[Key]

例如:我要

select 'Anz. Machine: ', count(*)           \
from w300 \
where length(w300.maschnr) in (6,7) \
and w300.maschnr LIKE ('%M%') \
and w300.maschnr not LIKE ('%FLP%') \
and w300.maschnr not LIKE ('%DFS%') \
and w300.maschnr not LIKE ('%SB%') \
and w300.maschnr not LIKE ('%C%') \
and w300.maschnr not LIKE ('%DL%') \
and w300.konto = 8000031 \
and w300.satzart = 1 \
and w300.fi_nr = 1 \

但只有那些 v600.vb = 8000001.

我不明白。谢谢。

我给你添加了一些图片。

Example

Example

Tables

Result

结果应该是如果 w300.konto = 8000001v600.vb = 8000001 Anz 的数量。机器 = 60

    select * from v600 where vb = 8000001;

v600 CUSTOMERS
fi_nr satzart konto vb

1 1 8000034 8000001

1 1 8000039 8000001

1 1 8000219 8000001

1 1 8000031 8000001

1 1 8000001 8000001

1 1 8000037 8000001

select * from w300 where konto = 8000039;

w300 MACHINES
fi_nr maschnr identnr satzart konto

1 #M2684D #M2684D 1 8000039

1 #M2719D #M2719D 1 8000039

select * from w300 where konto = 8000219;

w300 MACHINES
fi_nr maschnr identnr satzart konto

1 #M2581 #M2581 1 8000219

1 #M2591 #M2591 1 8000219

1 #M2599 #M2599 1 8000219

1 #M2600 #M2600 1 8000219

1 #M2601 #M2601 1 8000219

1 #M2604 #M2604 1 8000219

1 #M2605 #M2605 1 8000219

1 #M2606 #M2606 1 8000219


select 'Anz. Machine: ', count(*)
from w300
where length(w300.maschnr) in (6,7)
and w300.maschnr LIKE ('%M%')
and w300.maschnr not LIKE ('%FLP%')
and w300.maschnr not LIKE ('%DFS%')
and w300.maschnr not LIKE ('%SB%')
and w300.maschnr not LIKE ('%C%')
and w300.maschnr not LIKE ('%DL%')
and w300.konto = 8000039
and w300.satzart = 1
and w300.fi_nr = 1 ;

plus:

select 'Anz. Machine: ', count(*)
from w300
where length(w300.maschnr) in (6,7)
and w300.maschnr LIKE ('%M%')
and w300.maschnr not LIKE ('%FLP%')
and w300.maschnr not LIKE ('%DFS%')
and w300.maschnr not LIKE ('%SB%')
and w300.maschnr not LIKE ('%C%')
and w300.maschnr not LIKE ('%DL%')
and w300.konto = 8000219
and w300.satzart = 1
and w300.fi_nr = 1 ;

plus:

select 'Anz. Machine: ', count(*)
from w300
where length(w300.maschnr) in (6,7)
and w300.maschnr LIKE ('%M%')
and w300.maschnr not LIKE ('%FLP%')
and w300.maschnr not LIKE ('%DFS%')
and w300.maschnr not LIKE ('%SB%')
and w300.maschnr not LIKE ('%C%')
and w300.maschnr not LIKE ('%DL%')
and w300.konto = 8000034
and w300.satzart = 1
and w300.fi_nr = 1 ;

= Anz. Machine where v600.vb = 8000001

最佳答案

根据您的编辑,我认为您可能正在寻找类似

的内容
select 'Anz. Machine: ', count(*)           
from w300
where length(w300.maschnr) in (6,7)
...
and w300.konto IN (SELECT konto FROM v600
WHERE vb = 8000001)
... ;

关于mysql - 两张表的sql sum count(*),我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/36976482/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com