Сергей c0re

INSERT INTO product_attr (attr_7, attr_29)
WITH RECURSIVE
  param AS
(
  SELECT
    461  AS attr_7_start,  -- начальное значение для attr_7
    10   AS attr_7_num,    -- количество итераций в attr_7
    1097 AS attr_29_start, -- начальное значение для attr_29
    3    AS attr_29_num    -- количество итераций в attr_29
),
  attr29_iter (n) AS
(
  SELECT 0
  UNION ALL
  SELECT n + 1 FROM attr29_iter, param WHERE n < attr_29_num -1
),
 attr_7_iter (n) AS
(
  SELECT 0
  UNION ALL
  SELECT n + 1 FROM attr_7_iter, param WHERE n < attr_7_num -1
),
  gen_inc AS
(
  SELECT i7.n as inc7, i29.n as inc29
    FROM attr_7_iter i7, attr29_iter i29
)
SELECT attr_7_start + inc7, attr_29_start + inc29
  FROM gen_inc, param
  ORDER BY 1,2
;

INSERT INTO product_attr (product_id, product_price, product_old_price, attr_7, attr_29)
WITH RECURSIVE
  param AS
(
  SELECT
    461  AS attr_7_start,  -- начальное значение для attr_7
    14   AS attr_7_num,    -- количество итераций в attr_7
    1097 AS attr_29_start, -- начальное значение для attr_29
    3    AS attr_29_num    -- количество итераций в attr_29
),
  attr29_iter (n) AS
(
  SELECT 0
  UNION ALL
  SELECT n + 1 FROM attr29_iter, param WHERE n < attr_29_num -1
),
 attr_7_iter (n) AS
(
  SELECT 0
  UNION ALL
  SELECT n + 1 FROM attr_7_iter, param WHERE n < attr_7_num -1
),
  gen_inc AS
(
  SELECT i7.n as inc7, i29.n as inc29
    FROM attr_7_iter i7, attr29_iter i29
)
SELECT p.product_id, p.product_price, p.product_old_price, attr_7_start + inc7 AS attr_7, attr_29_start + inc29 AS attr_29
  FROM gen_inc, param, product p, category c
  WHERE p.product_id = c.product_id
    AND c.category_id NOT IN (214, 221, 220, 217, 216, 215, 48, 42, 40, 45, 46, 44, 50, 41, 43, 31, 178, 89, 47, 179, 177, 120, 121, 59, 58, 32, 37, 56, 53, 54, 55, 29, 28, 30, 176, 237, 113, 116, 175, 114, 196, 195, 197, 218, 219, 33, 93, 92, 91, 81, 90)
  ORDER BY 1, 4, 5
;

select
    lng.object_id,
    ltd.value as latitude,
    lng.value as longitude
  from obj_table lng, obj_table ltd
  where lng.field_name = 'longitude'
    and ltd.field_name = 'latitude'
    and lng.object_id = ltd.object_id
;

select
    object_id,
    max(case field_name when 'latitude' then value else null end) as latitude,
    max(case field_name when 'longitude' then value else null end) as latitude,
    max(case field_name when 'category' then value else null end) as category
  from obj_table
  group by object_id;

-- собираем в json:
select
    object_id,
	cast(replace(group_concat(json_object(field_name,value)),'},{',',') as json) json_obj
  from obj_table
  group by object_id
;

-- или так:
select
    object_id,
    cast(concat('{',group_concat( concat('"',field_name,'":"',value,'"') separator ',' ), '}') as json) as json_obj
  from obj_table
  group by object_id
;

-- извлекаем поля из json:
select
    object_id,
    json_extract(json_obj,'$.latitude') as latitude,
    json_extract(json_obj,'$.longitude') as latitude,
    json_extract(json_obj,'$.category') as category
  from (
select
    object_id,
    cast(replace(group_concat(json_object(field_name,value)),'},{',',') as json) json_obj
  from obj_table
  group by object_id
  ) t
;

select cal.cal_date, count(person)
  from some_table st
  right join (
  -- генерируем календарь - набор дат
    select *
      from (
        select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) cal_date
          from
            (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
            (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
            (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
            (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
            (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4
      ) v
      where cal_date between '2021-11-01' and '2021-11-30'
  ) cal on cal.cal_date = st.date_field
  group by cal.cal_date

SELECT '2021-11-01' + INTERVAL seq DAY FROM seq_0_to_29;

select cal.cal_date, count(person)
  from some_table st
  right join (
  -- генерируем календарь - набор дат
    SELECT '2021-11-01' + INTERVAL seq DAY as cal_date FROM seq_0_to_29
  ) cal on cal.cal_date = st.date_field
  group by cal.cal_date
;

-- For MySQL 8.0
select FLOW, OTPERIOD, LOG_DATE, LOG_TIME, ENTITY
  from (
    select
      FLOW, OTPERIOD, LOG_DATE, LOG_TIME, ENTITY,
      row_number() over (partition by ENTITY order by LOG_DATE desc, LOG_TIME desc) num
    from test
  ) tt
  where num = 1
;


-- For MySQL <=8.0
select t.*
  from test t
  inner join (
    select max(LOG_DATE) LOG_DATE, MAX(LOG_TIME) LOG_TIME, ENTITY
      from test
      group by ENTITY
      having concat(LOG_DATE,' ', LOG_TIME) = max(concat(LOG_DATE,' ', LOG_TIME))
) tt on tt.ENTITY = t.ENTITY and tt.LOG_DATE = t.LOG_DATE and tt.LOG_TIME = t.LOG_TIME
;


-- For MySQL <=8.0
select *
  from test t
  where concat(LOG_DATE,' ', LOG_TIME) = (
    select max(concat(LOG_DATE,' ', LOG_TIME))
      from test tt
        where tt.ENTITY = t.ENTITY
      group by ENTITY
  )
;

надо что бы при запросе вывел где status= true, и count_id = количество id за 30 дней.
|id|id_2|name|status|date| count_id |

• количество записей считается COUNT при группировке по полям (см. GROUP BY), в данном случае по интервалу дат
  
  
• но... в таком виде

  |id|id_2|name|status|date| count_id |

  вывести не получится! т.к. группировать по полям |id|id_2|name| не имеет смысла, они могут быть уникальны и никакого COUNT за интервал не получится, например как считать количество в интервале для id=1,2,3 ... ??? это группироваться никак не будет вообще, будет выводиться построчно для каждого id и количество будет Всегда равно =1 и так для всех id. определитесь что вам надо?! конкретно
  
  
• можно выбрать записи со статусом true и посчитать количество в интервале, для этого поле date привести в некий интервальный формат, либо если надо за ПОСЛЕДНИЕ 30 дней, то просто:
  

  SELECT count(id) count_id
    FROM tbl
    WHERE status = 'true'
      AND date >= now() - interval 30 day
  ;

  
  
  

$query1 = mysqli_query($link, "SELECT * FROM goods ORDER BY id");

SELECT
    c.title,
    ou.name,
    m.text,
    FROM_UNIXTIME(m.date) AS dt
  FROM chat_users cu
  INNER JOIN chat_users cuu ON cuu.chat_id = cu.chat_id AND cuu.user_id != cu.user_id
  INNER JOIN users ou ON ou.id = cuu.user_id
  INNER JOIN chat c ON c.id = cu.chat_id
  INNER JOIN messages m ON m.user_id = cuu.user_id AND m.chat_id = cu.chat_id
  WHERE cu.user_id = :ID_USER  -- ID пользователя по которому выбирать чаты.
  ORDER BY c.title ASC, m.date DESC

SELECT
    position, count(1) AS n
  FROM (
    SELECT
        position, row_number() OVER (PARTITION BY position) rn
      FROM tbl
  ) t
  GROUP BY position, (rn+1) div 2
;

SELECT
    e.*, c.`click`, c.`unique_click`
  FROM (
    SELECT
        DATE(FROM_UNIXTIME(`inserted_at`)) as `day`,
        SUM(IF(`type` = 2, `total_cost`, 0)) as `f_sum`,
        SUM(IF(`type` = 3, `total_cost`, 0)) as `d_sum`,
        SUM(IF(`type` = 4, `total_cost`, 0)) as `w_sum`,
      FROM `event`
      GROUP BY `day`
  ) e
  INNER JOIN (
    SELECT
        DATE(FROM_UNIXTIME(`inserted_at`)) as `day`
        SUM(IF(`type` = 1, 1, 0)) as `click`,
        SUM(IF(`type` = 2, 1, 0)) as `unique_click`,
      FROM `click`
      GROUP BY `day`
  ) c ON e.`day` = c.`day`
  ORDER BY e.`day` DESC
  LIMIT 10
;

$arr = [
    0 => "UT",
    1 => "409"
];

$s = join("%", $arr)."%";

$db->where('model', 'like', $s);

SELECT
    name,
    sku,
    CONCAT('https://домен/', IF(ua2.keyword IS NULL,'',CONCAT(ua2.keyword, '/')), IF(ua3.keyword IS NULL,'',CONCAT(ua3.keyword, '/')), IF(ua4.keyword IS NULL,'',CONCAT(ua4.keyword, '/')), ua1.keyword) AS url
  FROM (
    SELECT
        name,
        p.sku,
        CONCAT( 'product_id=', p.product_id ) AS product_query,
        CONCAT( 'category_id=', pc.category_id ) AS category_query,
        CONCAT( 'series_id=', ps.series_id ) AS series_query,
        CONCAT( 'subcategory_id=', psc.subcategory_id ) AS subcategory_query
      FROM `oc_product_description` pd
      LEFT JOIN oc_product p ON (p.product_id=pd.product_id)
      LEFT JOIN oc_product_to_category pc ON (pc.product_id=p.product_id)
      LEFT JOIN oc_product_to_series ps ON (ps.product_id=p.product_id)
      LEFT JOIN oc_product_to_subcategory psc ON (psc.product_id=p.product_id)
      WHERE p.date_available <= NOW()
        AND p.status = '1'
  ) pd
  LEFT JOIN oc_url_alias ua1 ON ( pd.`product_query` = ua1.`query` )
  LEFT JOIN oc_url_alias ua2 ON ( pd.`category_query` = ua2.`query` )
  LEFT JOIN oc_url_alias ua3 ON ( pd.`series_query` = ua3.`query` )
  LEFT JOIN oc_url_alias ua4 ON ( pd.`subcategory_query` = ua4.`query` )
;