当前位置: 动力学知识库 > 问答 > 编程问答 >

mysql - SQL Return rows where column 1 contains X and Y

问题描述:

i know this must be in SQL 101, but i need to return rows where a column contains both X And Y, where an ID is duplicated.

Example Table.

 ACCOUNT | Activity

-------------------------

1 | Email

1 | Appointment

2 | Email

2 | Email

3 | Email

3 | Appointment

I need the SQL that will return to me accounts 1 and 3 only, like so:

 ACCOUNT | Activity

-------------------------

1 | Email

1 | Appointment

3 | Email

3 | Appointment

In pseudo code it's (WHERE ID occurs with both 'Appointment' AND 'Email')

Account 2 should not be returned, since there is no row in the table WHERE Account = 2 AND Activity = 'Appointment'.

I hope that makes sense, and would really appreciate your input.

Thank in advance.

EDIT BELOW THE LINE


Thanks everybody for your suggestions, i very much like @rafa 's suggestion of using count distinct, but of course the query is more complex than first suggested. The first table is actually a result set from another query, query below including @rafa suggestion, but the query doesn't work, i understand i need to invoke a sub query, but am unsure where or how, thanks again:

SELECT T.ACCOUNT, T.COMPANY, H.RESPONSE, H.DAT_ AS Resp_Date, H.USERNAME, (date_format(P.ENDDATE,'%M')) AS Renewal, T.OWNER, COUNT(DISTINCT(H.ACTIVITY)) AS Dis_Act, H.CustomerStatus, H.Contact, A.ANAL14 AS APPDATE

FROM TELRCMxxx T LEFT JOIN TELCOMxxx H ON T.ACCOUNT = H.ACCOUNT LEFT JOIN ACCSTOxxx P ON T.ACCOUNT = P.ACCOUNT LEFT JOIN RCMANLxxx A ON T.ACCOUNT = A.ACCOUNT

WHERE (H.ACTIVITY in ('Appointment', 'email'))

Group by Account

having Dis_Act > 1

J.


Thanks everyone.

网友答案:

Here's one way to do it. Use an EXISTS predicate that uses a correlated subquery to check for the existence of the "other" row. This assumes that you only want to return rows that have values of 'Email' and 'Appointment' in the Activity column, and to exclude rows that have any other value for activity. (This isn't the most efficient way to do it.)

SELECT t.account
     , t.activity
  FROM example_table t
 WHERE t.activity IN ('Email','Appointment')
   AND EXISTS ( SELECT 1
                  FROM example_table d
                 WHERE d.account = t.account
                   AND d.activity = 
                         CASE t.activity
                           WHEN 'Email' THEN 'Appointment'
                           WHEN 'Appointment' THEN 'Email'
                        END
              )

ADDITION

Here's the approach above applied to the original query (which is later supplied as a comment on another answer...)

SELECT t.ACCOUNT
     , t.COMPANY
     , h.RESPONSE
     , h.DAT_ AS Resp_Date
     , h.USERNAME
     , DATE_FORMAT(p.ENDDATE,'%M') AS Renewal
     , t.OWNER
     , h.CustomerStatus
     , h.Contact
     , a.ANAL14 AS APPDATE 
  FROM TELRCMxxx t
  JOIN TELCOMxxx h
    ON t.ACCOUNT = h.ACCOUNT
   AND h.ACTIVITY in ('Appointment','email')
   AND EXISTS 
       ( SELECT 1
           FROM TELCOMxxx b
          WHERE b.ACCOUNT = h.ACCOUNT
            AND b.ACTIVITY = CASE h.ACTIVITY
                               WHEN 'email' THEN 'Appointment'
                               WHEN 'Appointment' THEN 'email'
                             END
       )
  LEFT
  JOIN ACCSTOxxx p
    ON t.ACCOUNT = p.ACCOUNT
  LEFT
  JOIN RCMANLxxx a
    ON t.ACCOUNT = a.ACCOUNT

NOTES: original query has a LEFT join to h, but the "outerness" of the join operation is negated by the WHERE clause, which effectively verifies h.ACTIVITY IS NOT NULL. The LEFT keyword is removed, and the h.ACTIVITY IN ('Appointment','email') predicate is moved from the WHERE clause to the ON clause of the join. But that doesn't really change anything about the query.

The change to the query is the addition of an "EXISTS" predicate that checks for the existence of another row in h, matching on ACCOUNT, and matching either 'Appointment' or 'email', as the opposite of the value in the row from h being checked.

Note that this:

            AND b.ACTIVITY = CASE h.ACTIVITY 
                             WHEN 'email' THEN 'Appointment'
                             WHEN 'Appointment' THEN 'email'
                             END

is equivalent to:

            AND ( ( b.ACIVITY = 'email' AND h.ACTIVITY = 'Appointment' )
                 OR
                  ( b.ACIVITY = 'Appointment' AND h.ACTIVITY = 'email' )
                )

END ADDITION


If you need to return all rows for the account, including other values for activity, then you remove the t.Activity IN predicate from the WHERE clause on the outer query, and just check for the existence of both an 'Email' and 'Appointment' row for that account:

SELECT t.account
     , t.activity
  FROM example_table t
 WHERE EXISTS ( SELECT 1
                  FROM example_table e
                 WHERE e.account = t.account
                   AND e.activity = 'Email'
              )
   AND EXISTS ( SELECT 1
                  FROM example_table a
                 WHERE a.account = t.account
                   AND a.activity = 'Appointment'
              )

This is not the most efficient approach, but it will return the specified result.

For large sets, a (usually) more efficient approach is to use a JOIN operation.

To get a list of distinct account values that have both 'Email' and 'Appointment' rows:

SELECT e.account
  FROM example_table e
  JOIN example_table a
    ON a.account = e.account
       AND a.activity = 'Appointment'
 WHERE e.activity = 'Email'
 GROUP BY e.account

To get all rows from the table for those accounts:

SELECT t.account
     , t.activity
  FROM example_table t
  JOIN ( SELECT e.account
           FROM example_table e
           JOIN example_table a
             ON a.account = e.account
            AND a.activity = 'Appointment'
          WHERE e.activity = 'Email'
         GROUP BY e.account
       ) s
    ON s.account = t.account

If you only want to return rows with particular values for activity, you can add a WHERE clause, e.g.

 WHERE t.activity IN ('Email','Appointment','Foo')
网友答案:

A very naive approach would be:

SELECT
    *
FROM MyTable t1
WHERE EXISTS
    (
        SELECT 1 FROM MyTable WHERE ACCOUNT = t1.ACCOUNT AND Activity = 'Email'
    )
    AND EXISTS
    (
        SELECT 1 FROM MyTable WHERE ACCOUNT = t1.ACCOUNT AND Activity = 'Appointment'
    )
网友答案:

This can probably be done several ways. The first way that comes to mind is to use the Exists clause a couple tiems:

Select  *
From    MyTable t1
Where   Exists  (
                    Select  Null
                    From    MyTable t2
                    Where   t1.Account = t2.Account
                        And t2.Activity = 'Email'
                )
    And Exists  (
                    Select  Null
                    From    MyTable t3
                    Where   t1.Account = t3.Account
                        And t3.Activity = 'Appointment'
                )

sql fiddle

Or with a couple self joins:

Select  t1.*
From    MyTable t1
join    MyTable t2
    on  t1.Account = t2.Account
    and t2.Activity = 'Email'
join    MyTable t3
    on  t1.Account = t3.Account
    and t3.Activity = 'Appointment'

sql fiddle

网友答案:

select account, activity from x where account in (select account from

(select x.account, x1.account as account1 from x join x as x1 on x.account= x1.account and x.activity <>x1.activity) summary1

where summary1.account1 is not null )

Don't know mysql but this is generic sql that works in sybase/MS type db.

网友答案:

Will this work ?

select src.account, src.activity
from
(
select account, activity, COUNT(activity) as cnt
from mytable as t1
group by account, activity
) as src 
where cnt = 1
order by src.account
网友答案:

A simple inner join will work

select account from MyTable as T1
inner join MyTable as T2 on (T2.Account = T1.Account and T2.Activity = 'Appointment')
where T1.Activity = 'EMail'

ADDED

Of course, an index on Activity will be useful if performance is an issue, but that is pretty much a given regardless of how you write the query

网友答案:

Assuming the table is named something like AccountActivity:

SELECT * 
FROM AccountActivity 
WHERE Account IN (
    SELECT AccountID 
    FROM AccountActivity 
    WHERE Activity = 'Email')
AND Account IN (
    SELECT AccountID 
    FROM AccountActivity 
    WHERE Activity = 'Appointment')
网友答案:

Simple and elegant solution using GROUP BY with HAVING

SELECT account, COUNT(DISTINCT(activity)) AS diff_acts
FROM account_activity
WHERE activity IN ('Appointment', 'Email')
GROUP BY account
HAVING diff_acts > 1

SQL Fiddle

Whereas if you want all the rows, do it as below

SELECT * 
FROM account_activity
WHERE account IN (
  SELECT account
  FROM account_activity
  WHERE activity IN ('Appointment', 'Email')
  GROUP BY account
  HAVING COUNT(DISTINCT(activity)) > 1 )

SQL Fiddle

分享给朋友:
您可能感兴趣的文章:
随机阅读: