mysql - 根据 NULL 字段从表中选择公司 ID 之间的公共(public)列？

Question

表格结构如下

calling_numb |called_num|cell_id|calling_loc|called_loc|company|cell_type|called_reg|c_site
+937779832423|+93799234323|C8435|   NULL    | NULL     | 77    | Nokia   | 44       |443543
+937883432424|+93700335234|S6700| 4     | 9        | 788   | 435341  | NULL     | NULL
+937999343324|+93788324234|W580 | Ghazni    |   Herat  | 799   | Kabul   | NULL     | NULL
+937003556322|+93788342343|K860 | 01        |   NULL   | 700   | NULL    | NULL     | 6823
+937778342453|+93700453423|L900 | 05        | 12       | 77    | Samsung | NULL     | NULL
+937994354356|+93789345435|K334 | Ghazni    | Herat    | 799   | Nokia   | NULL     | 3345
+937004353455|+93799033453|S6790| NULL      | NULL     | 700   | NULL    | xYZ      |34543 

真实表中的表的简要结构它有很多字段，现在我想通过检查公司的值(value)来获得所有公司共有的那些字段的列名，例如:

select column_name from table where company = 77 
intersect 
select column_name from table where company = 799 
intersect 
select column_name from table where company = 700 
intersect 
select column_name from table where company = 788

我想要的输出是calling_number, called_number, cell_id 这四个公司通用

i receive all information of a SIM from telecom companies for example i save information from company A and for other SIM from company B and the same for C , and D these company has some same field for data they store that i save those fields in the same column for all company but also each company has seprate field that is not the same for other company now what i want to get is the column name from table that is same for all company and their value is not null for all company

Answer 1

下面的代码肯定对你有用。

$result = mysqli_query($con,"SELECT * FROM information_schema.columns where table_name='your_table_name'");
$columns = mysqli_fetch_array($result);/*This will retreive all columns from the specified tablename*/
$outcols=array();
$count=count($columns);
$i=0;
$inc=0;
while($i<$count)
{
$result=mysqli_query($con,"SELECT ".$columns[$i]." FROM your_table_name WHERE ".$columns[$i]." IS NULL");
$row = mysqli_fetch_array($result);
if(count($row)<=0)
{
$outcols[$inc]=$columns[$i];
$inc++;
}
$i++;
}
print_r($result);

The above code is coded by my own.Check it out.Iam sure it will help you.If you are using PHP to access MySQL Database. The above code will print all column names whose data are not null in the specified table.