mysql - SQL - 在具有许多条件的每列之间打印许多单词

Question

好的，首先我有不可变的值:

4   8   16   32   64   128   256

我有一张类似的 table :

+----+------+---------------------+-------------+
| id |   full_name  |  club_name  |  y_of_birth |
+----+------+---------------------+-------------+
| 1  | Ahmed Sayed  |   El Ahly   |    2000     |
+----+------+---------------------+-------------+
| 2  | Kareem Gaber |   El Ahly   |    2000     |
+----+------+---------------------+-------------+
| 3  | Maher Zein   |   El Ahly   |    2003     |
+----+------+---------------------+-------------+
| 4  | Mohab Saeed  |   El Ahly   |    2003     |
+----+------+---------------------+-------------+
| 5  | Kamal saber  |   wadi dgla |    2000     |
+----+------+---------------------+-------------+
| 6  | gamel kamel  |   el-nasr   |    2002     |
+----+------+---------------------+-------------+
| 7  | omar galal   |   Cocorico  |    2000     |
+----+------+---------------------+-------------+
| 8  | Kamal saber  |   Cocorico  |    2004     |
+----+------+---------------------+-------------+
| 9  | Mohamed gad  |   Ismaily   |    2000     |  
+----+------+---------------------+-------------+
| 10 | ehab zeyad   |   Ismaily   |    2005     |
+----+------+---------------------+-------------+
| 11 | moaz maged   |   Smouha    |    2001     |
+----+------+---------------------+-------------+
| 12 | mazen mahmod |   elmasry   |    2006     |
+----+------+---------------------+-------------+
| 13 | ahmed shawky |   Petroget  |    2002     |
+----+------+---------------------+-------------+
| 14 | shaker ali   |   Petroget  |    2007     |
+----+------+---------------------+-------------+

我尝试使用查询过滤数据库中的数据

select full_name,club_name from players where y_of_birth=2000

结果是5玩家应该是这样的:

+--------------+--------------+
|   full_name  |  club_name   |
+--------------+--------------+
| Ahmed Sayed  |   El Ahly    |
+----+------+--+--------------+
| Kareem Gaber |   El Ahly    |
+------+-------+--------------+
| Kamal saber  |  wadi dgla   |
+------+-------+--------------+
| omar galal   |   Cocorico   |
+------+-------+--------------+
| Mohamed gad  |  Ismaily     |
+------+-------+--------------+

好的条件是:

如果结果大于 4 > 4 小于 8 <= 8将结果放入 8在我们的例子中，结果是 5意思是 8 - 5 = 3意思是把这个词迭代3次，结果应该是这样的:

+--------------+-------------+
|   full_name  |  club_name  |
+--------------+-------------+
| Ahmed Sayed  |  El Ahly    |
+----+------+--+-------------+
| **ANY WORD** |             |
+--------------+-------------+
| Kareem Gaber |  El Ahly    |
+------+-------+-------------+
| Kamal saber  |  wadi dgla  |
+------+-------+-------------+
| **ANY WORD** |             |
+--------------+-------------+ 
| omar galal   |  Cocorico   |
+------+-------+-------------+
| Mohamed gad  |  Ismaily    |
+------+-------+-------------+
| **ANY WORD** |             |
+--------------+-------------+

注意:**ANY WORD** 之间请勿相邻到上面的例子:

+------+-------+
| **ANY WORD** |
+--------------+
| **ANY WORD** |
+--------------+

或

club_name 之间没有相邻到上面的例子:

+------+-------+
|    El Ahly   |
+--------------+
|    El Ahly   |
+--------------+

更新:

又是一个例子

它基于这些数字

4 8 16 32 64 128 256

而条件是:

1. 如果查询结果 <= 4和 > 2意思是 (4 - the number of query result)

例子:如果查询结果是3所以4 - 3 = 1所以1是 **ANY WORD** 的数字所以所需的输出将是这样的:

+--------------+-------------+
|   full_name  |  club_name  |
+--------------+-------------+
| Ahmed Sayed  |  El Ahly    |
+----+------+--+-------------+
| **ANY WORD** |             |
+--------------+-------------+
| Kareem Gaber |  El Ahly    |
+------+-------+-------------+
| Kamal saber  |  wadi dgla  |
+--------------+-------------+

1. 另一个示例与8if 查询结果 <= 8和 > 4意思是 (8 - the number of query result)

例如:查询结果是5所以8 - 5 = 3所以3是 **ANY WORD** 的数字所以想要的输出会是这样的

+--------------+-------------+
|   full_name  |  club_name  |
+--------------+-------------+
| Ahmed Sayed  |  El Ahly    |
+----+------+--+-------------+
| **ANY WORD** |             |
+--------------+-------------+
| Kareem Gaber |  El Ahly    |
+------+-------+-------------+
| Kamal saber  |  wadi dgla  |
+------+-------+-------------+
| **ANY WORD** |             |
+--------------+-------------+ 
| omar galal   |  Cocorico   |
+------+-------+-------------+
| Mohamed gad  |  Ismaily    |
+------+-------+-------------+
| **ANY WORD** |             |
+--------------+-------------+

4 以此类推和 16和 32和 64 ..等直到256 .

任何帮助将不胜感激。

Answer 1

新的和改进的(第 3 版如何)使用变量并使用与 here 基本相同的技巧:

SELECT
  IF(is_real, '**ANY WORD**', full_name) AS full_name,
  IF(is_real, '', club_name) AS club_name
FROM
  (
    SELECT
      full_name,
      club_name,
      (@row_num2:= @row_num2 + 1) AS row_num
    FROM
      (
        SELECT p3.*
        FROM
          (
        SELECT
          p2.*,
          (@row_num := @row_num + 1) AS row_num
        FROM
          (
            SELECT *
            FROM players AS p1
            WHERE y_of_birth = 2000
          ) AS p2
        CROSS JOIN
          (
            SELECT
              @row_num := 0,
              @count := (SELECT COUNT(*) FROM players WHERE y_of_birth = 2000)
          ) AS vars
        ORDER BY club_name
      ) AS p3
    ORDER BY row_num % FLOOR(@row_num / 2), row_num
  ) AS p4
CROSS JOIN
  (
    SELECT
      @row_num2 := -1,
      @extra := GREATEST(2, POW(2, CEIL(LOG2(@count)))) - @count) AS vars
  ) AS data
LEFT JOIN
  (
    (SELECT 1 AS is_real)
    UNION ALL
    (SELECT 0 AS is_real)
  ) AS filler
ON
  MOD(row_num, FLOOR(@count / @extra)) = 0 AND
  row_num / FLOOR(@count / @extra) < @extra
ORDER BY row_num, is_real

对于您提供的示例数据，这会产生如下内容:

+--------------+-----------+
| full_name    | club_name |
+--------------+-----------+
| Ahmed Sayed  | El Ahly   |
| **ANY WORD** |           |
| Mohamed gad  | Ismaily   |
| **ANY WORD** |           |
| omar galal   | Cocorico  |
| **ANY WORD** |           |
| Kareem Gaber | El Ahly   |
| Kamal saber  | wadi dgla |
+--------------+-----------+

这应该适用于任何大小的结果；只需将条件 (y_of_birth = 2000) 更改为您想要的任何条件。我升级到 MySQL 5.6 来测试这个(实际上结果有一点点不同)。

基本技巧是使用 UNION 创建具有静态值的两行表(在本例中为 1 和 0)然后 LEFT JOIN 将其加入实际结果的次数以填充到 2 的幂。这意味着我们已经计算了结果中每一行的数量(称为 row_num) 以便我们可以正确地制定连接条件。最后，这会每隔这么多行产生一个重复行；最后一点是通过检查我们是在真实的还是假的(1 或 0) 行。

这应该可以防止来自同一支球队的球员彼此相邻，除非因为一支球队的球员太多而无法做到这一点；有关如何执行此操作的更多信息，请参阅上面的链接。基本思路是按俱乐部排序，然后从该列表的前半部分和后半部分交替挑选。

最后一个技巧是找出要加入虚拟行的数量和位置。在尝试了几件事之后，我意识到这实际上非常简单:只需加入每一行，直到我们达到所需的虚拟行数(@extra)。但是，这会将所有虚拟行打包在结果的顶部；要将它们分散得更多(不是完全分散，而是分散得更多)，计算我们需要添加一个的频率(FLOOR(@count/@extra))，然后每隔那么多行放置一个(ON 条件的第一部分)直到添加足够的内容(第二部分)。