如何在MySQL中循环表？

I have a MySQL table with the following schema:

我有一个MySQL表,其中包含以下模式:

+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| id           | int(11)     | NO   | PRI | NULL    | auto_increment |
| country_dest | int(11)     | YES  |     | NULL    |                |
| type         | varchar(56) | YES  |     | NULL    |                |
| version      | varchar(56) | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+

For every country_dest there will be several type - business, tourist, etc (but each country_dest may have different or totally unique types).

对于每个country_dest,将有几种类型 - 商业,旅游等(但每个country_dest可能有不同或完全独特的类型)。

I want to make sure that for every type, in every country there is a particular value of version (the same value eg 'complete').

我想确保对于每种类型,在每个国家都有特定的版本值(相同的值,例如'完整')。

So for instance:

例如:

+----+--------------+------------+----------+
| id | country_dest | type       | versions |
+----+--------------+------------+----------+
|  1 |            8 | business   | single   |
|  2 |            8 | business   | complete |
|  3 |            8 | tourist    | single   |
|  4 |            8 | tourist    | complete |
|  5 |            8 | diplomatic | single   |
|  6 |           14 | business   | single   |
|  7 |           14 | business   | complete |
|  8 |           14 | private    | single   |
|  9 |           31 | business   | double   |
| 10 |           31 | business   | complete |
+----+--------------+------------+----------+

If that was the table contents, then the query would return that:

如果那是表内容,那么查询将返回:

country_dest: 8 with type: diplomatic, and country_dest: 14 with type: private

country_dest:8,类型:foreign,country_dest:14,类型:private

don't have the complete value.

没有完整的价值。

Output should be:

输出应该是:

+---------------+---------------+
| country_dest  |      type     |
+---------------+---------------+
|             8 |   diplomatic  |
|            14 |   private     |
+---------------+---------------+

It seems like it would be great to loop through this in a for loop, but that's not how it's done. I've tried lots of solutions but I can't get them to work and I'm sure there must be a tried and tested way of doing this. This is my latest effort:

看起来在for循环中循环遍历会很棒,但这不是它的完成方式。我已经尝试了很多解决方案,但是我无法让它们工作,我确信必须有一种经过验证的方法来做到这一点。这是我最近的努力:

SELECT form_dest, form_vtype, form_ispack FROM _forms f
WHERE form_dest IN 
    (SELECT DISTINCT dest.form_dest FROM _forms) dest
AND form_vtype IN 
    (SELECT DISTINCT form_vtype FROM _forms as type)
AND NOT EXISTS  
    (SELECT * FROM _forms 
    WHERE f.form_dest = dest.form_dest 
    AND f.form_type = type.form_type 
    AND f.form_ispack = "Yes") 

but I get errors.

但我得到错误。

Grateful for any help on this. Cheers.

感谢任何帮助。干杯。

1 个解决方案

#1

Using a NOT EXISTS should be enough to get the results you require.

使用NOT EXISTS应该足以获得您需要的结果。

To get the entire _forms record, you can wrap the statement into a subselect and join it again with _forms.

要获取整个_forms记录,可以将语句包装到子选择中并使用_forms再次连接它。

SQL Statement

SELECT  country_dest
        , type
FROM    _forms invalid
WHERE   NOT EXISTS (        
          SELECT  country_dest
                  , type
          FROM    _forms valid
          WHERE   versions = 'complete'
                  AND valid.country_dest = invalid.country_dest
                  AND valid.type = invalid.type
        )          
GROUP BY
        country_dest
        , type

or equivalent using a DISTINCT

或使用DISTINCT的等效物

SELECT  DISTINCT country_dest
        , type
FROM    _forms invalid
WHERE   NOT EXISTS (        
          SELECT  country_dest
                  , type
          FROM    _forms valid
          WHERE   versions = 'complete'
                  AND valid.country_dest = invalid.country_dest
                  AND valid.type = invalid.type
        )          

Test script (SQL Server)

;WITH _forms (country_dest, type, versions) AS (
  SELECT  8, 'business', 'single'
  UNION ALL SELECT  8, 'business', 'complete'
  UNION ALL SELECT  8, 'tourist', 'single'
  UNION ALL SELECT  8, 'tourist', 'complete'
  UNION ALL SELECT  8, 'diplomatic', 'single'
  UNION ALL SELECT 14, 'business', 'single'
  UNION ALL SELECT 14, 'business', 'complete'
  UNION ALL SELECT 14, 'private', 'single'
  UNION ALL SELECT 31, 'business', 'double'
  UNION ALL SELECT 31, 'business', 'complete'
)
SELECT  country_dest
        , type
FROM    _forms f
WHERE   NOT EXISTS (        
          SELECT  country_dest
                  , type
          FROM    _forms f2
          WHERE   versions = 'complete'
                  AND f2.country_dest = f.country_dest
                  AND f2.type = f.type
        )          
GROUP BY
        country_dest
        , type