I am dealing with an issue that I have to backtrack as the entire project is already in production and system has been used for a while.

我正在处理一个我必须回溯的问题,因为整个项目已经在生产中并且系统已经使用了一段时间。

I need to backtrack all the data with the following parametars.

我需要使用以下参数来回溯所有数据。

Select * from table where bundle = 5 and count(bundle) >= 3

从表中选择*,其中bundle = 5并且count(bundle)> = 3

This will be a joined table so technically I need count of bundles greater than 2 with same transaction.

这将是一个连接表,所以从技术上讲,我需要计算大于2的捆绑包含相同的事务。

id | transaction | bundle
-------------------------
1  | 123         | 5
3  | 234         | 15
12 | 1111        | 5
13 | 1111        | 15
17 | 1111        | 5
18 | 1111        | 5

My code so far

我的代码到目前为止

select * from table_i as ti
right join table_r as tr
on tr.id = ti.t_id
where ti.type_id = x and ti.bundle = 5 and ti.online = 1 and count(ti.bundle) >=5

Thanks

EDIT REAL CODE:

SELECT ti.*, tr.*
FROM ticket_items AS ti
INNER join transactions as tr
ON tr.id = ti.trans_id
INNER JOIN
(
    SELECT tis.trans_id, COUNT(tis.bundle) AS bundle_count
    FROM ticket_items as tis
    INNER join transactions as trs
    ON trs.id = tis.trans_id
    WHERE tis.type_id = 2
    AND tis.bundle = 5 
    AND tis.online = 1 
    HAVING bundle_count > 2
) sub0
ON sub0.trans_id = ti.trans_id
WHERE ti.type_id = 2
AND ti.bundle = 5 
AND ti.online = 1

Result: 1328 1 1 766 2 5 25 1 1 2015-10-26 20:26:41 2015-10-27 00:00:02 0 766 1 0 John Doe 123-123-1234 NULL email@email.com NULL NULL NULL NULL 1 164 Cedar Square NULL 123 rrt city province country 125 2015-10-26 20:26:41 2015-10-26 20:26:41 125.00 0.00 0.00 0.00 0 1

结果:1328 1 1 766 2 5 25 1 1 2015-10-26 20:26:41 2015-10-27 00:00:02 0 766 1 0 John Doe 123-123-1234 NULL email@email.com NULL NULL NULL NULL 1 164 Cedar Square NULL 123 rrt city province country 125 2015-10-26 20:26:41 2015-10-26 20:26:41 125.00 0.00 0.00 0.00 0 1

Table ticket_items:

id | lot | system | trans_id | type | bundle | price | print | online | date                | update              | void
1    1     2        1          1      1        100     1       0        2015-10-01 23:30:12   2015-10-03 18:49:25   0
2    1     2        1          2      15       50      1       0        2015-10-01 23:30:12   2015-10-03 16:48:15   0
3    1     3        2          1      1        100     1       0        2015-10-02 00:13:57   2015-10-02 00:22:17   1
4    1     3        2          2      15       50      1       0        2015-10-02 00:13:57   2015-10-02 00:19:17   1

Table transactions:

id | lot_id | cust | first | last| number |||||||
1  | 1      | 23   | john  | doe | 123

3 个解决方案

#1

If I understand correctly, you want to find transactions where there are more than 3 with that transaction code for a bundle of 5 and with online = 1, and then bring back all the rows from the 2 tables for that transaction with a bundle of 5 and with online = 1

如果我理解正确的话,你想找到一个交易,其中3个包的交易代码超过3个,并且在线= 1,然后用5个包来回收该交易的2个表中的所有行并且在线= 1

If so, a sub query to get the transactions with a count of more than 3 and then join that back against the 2 tables:-

如果是这样,一个子查询来获取计数超过3的事务,然后将其连接到2个表: -

SELECT ti.*, tr.*
FROM table_i AS ti
INNER join table_r as tr
ON tr.id = ti.t_id
INNER JOIN
(
    SELECT tis.transaction, COUNT(tis.bundle) AS bundle_count
    FROM table_i as tis
    INNER join table_r as trs
    ON trs.id = tis.t_id
    WHERE tis.type_id = x 
    AND tis.bundle = 5 
    AND tis.online = 1 
    GROUP BY tis.transaction
    HAVING bundle_count >= 3
) sub0
ON sub0.transaction = ti.transaction
WHERE ti.type_id = x 
AND ti.bundle = 5 
AND ti.online = 1

#2

I'm a little confused by the wording of your question, but try this:

我对你的问题的措辞感到有些困惑,但试试这个:

SELECT ti.id, ti.transaction, count(ti.bundle)
FROM table_i as ti
JOIN table_r as tr
  ON tr.id = ti.id
WHERE ti.type_id = x 
  AND ti.bundle = 5 
  AND ti.online = 1 
  AND count(ti.bundle) >=5

#3

I think you need to use having. Something like this:

我认为你需要使用。像这样的东西:

select ti.*, count(ti.bundle) from table_i as ti
right join table_r as tr
on tr.id = ti.t_id
where ti.type_id = x and ti.bundle = 5 and ti.online = 1
group by ti.bundle
having count(ti.bundle) >=5; /* or >= 3 depending on which you're using now*/

#1