Question

let data = await Category.findAll({
  where: { list_category_id: body.category_id },
  attributes: {
    include: [
      [
        sequelize.literal(`COALESCE (
          CASE WHEN youtube_id_id IS NOT NULL THEN ROW_NUMBER() OVER (ORDER BY random(), youtube_id_id) END,
          CASE WHEN instagram_id_id IS NOT NULL THEN ROW_NUMBER() OVER (ORDER BY random(), instagram_id_id) END )`),
        "row_num",
      ],
    ],
  },
  order: [sequelize.literal("row_num")],
});

在一行中,只有一种数值是: you_id_id_id 或 instagram_id_id_id_,我来这里是为了使我能够每当一栏就获得用于tube_id_id_和 instagram_id_id_id_的数值,但是这只是没有ERP的随机()操作。每次我执政时,我都会取得随机结果,与此同时,我还要确保有两种价值的增长。我如何做这项工作?

EDIT:

表3

export const Category = sequelize.define(
  "category",
  {
    id: {
      type: DataTypes.UUID,
      defaultValue: sequelize.literal("gen_random_uuid()"),
      primaryKey: true,
    },
    score: {
      type: DataTypes.FLOAT,
      allowNull: false,
    },
    createdAt: {
      type: DataTypes.DATE,
      defaultValue: sequelize.literal("NOW()"),
    },
    updatedAt: {
      type: DataTypes.DATE,
      defaultValue: sequelize.literal("NOW()"),
    },
  },
  {
    underscored: true,
    tableName: "Category",
  }
);

。

抽样数据:

id	youtube_id_id	instagram_id_id
uuid		uuid
uuid	uuid
uuid	uuid
uuid		uuid

预期产出是描述的比喻,我希望每个外国钥匙的数据,即我只将数据限制在2个左右,因此,我总是掌握对两个钥匙都具有价值的增长。

基本上,我认为我所希望的是,当ROW_NUMBER(NUMBER)编号从1开始,而不是从第1行开始时,它把它排成一个任意的行子并保持下去。这应该让我随机地得出结果,这是有办法实现这一目标?

Answer 1

我确定这一点,只是不得不随意按一栏和组别进行分割。

COALESCE (CASE WHEN youtube_id_id IS NOT NULL THEN ROW_NUMBER() OVER (PARTITION BY youtube_id_id IS NOT NULL ORDER BY random()) END,
          CASE WHEN instagram_id_id IS NOT NULL THEN ROW_NUMBER() OVER (PARTITION BY youtube_id_id IS NOT NULL ORDER BY random()) END)

Answer 2

我想在我执政时每次都取得随机结果,同时确保有两种价值的增长。

您可提出两个查询和<代码>union。

(
select *
from your_table
where youtube_id is null
  and instagram_id is not null
order by random()
limit 1
)

union

(
select *
from your_table
where youtube_id is not null 
  and instagram_id is null
order by random()
limit 1
)

https://dbfiddle.uk/tnrU9a0q” rel=“nofollow noreferer”>Demonstration 。

Sequelize don t direct support union, but You can simulate it by .combining two questions with a Promise.all。

友情链接